Data Base Administrative

Answer Details :

Below answers for reference of interview preparations of Oracle DBA:

Answer Details :

RAC Node Eviction Types (NHB/DHB) ::::

css misscount in Cluster: The CSS misscount is the maximum time, in seconds, that a cluster heartbeat (messages sent between nodes over the network interconnect or through voting disk; the prime indicator of connectivity), can be missed before entering into a cluster reconfiguration to evict the node.

Two types :

1.css misscount For Network heartbeat

2.disk misscount for Disk heartbeat

Default value for css misscount is 30 seconds.

To check value of this parameter

[oracle@db02 ~]$ crsctl get css misscount

CRS-4678: Successful get misscount 60 for Cluster Synchronization Services.

reboottime: The amount of time allowed for a node to complete a reboot after the CSS daemon has been evicted. (i.e. how long does it take for the machine to completely shut-down when you do a reboot -f -n)

Default value 3 seconds

You can check value of reboottime parameter by

[oracle@db02 ~]$ crsctl get css reboottime

CRS-4678: Successful get reboottime 3 for Cluster Synchronization Services.

disktimeout: Disk Heartbeat is internally calculated. The value is different across different releases of Oracle. Disk heart beat time-out should be set maximum time allowed for Voting Disk IO to be completed.

Default value is 200 seconds

To check the value for disktimeout parameter :

[oracle@db02 ~]$ crsctl get css disktimeout

CRS-4678: Successful get disktimeout 200 for Cluster Synchronization Services.

With 11gR2, these settings can be changed online without taking any node down:

1) Execute crsctl as root to modify the misscount:

$CRS_HOME/bin/crsctl set css misscount n

$CRS_HOME/bin/crsctl set css reboottime n

$CRS_HOME/bin/crsctl set css disktimeout n

1.css misscount For Network heartbeat

Cause : Network failure (e.g. packet errors/retransmits) or Network latency (e.g. packet snd/rcv high ms with retransmits) between nodes,

heartbeats are issued once per sec.

Note :

•If the OS messages file reboot timestamp < missed checkin timestamp then the node eviction was likely not due to these missed checkins.

2.disk misscount for Disk heartbeat

Cuause :Majority of the voting disks are unavailable or I/O latency, heartbeats are issued once per sec.

Note

•If strictly more than half of the voting disks are up and contain consistent information, the CSS can use that consistent data.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Add Service in RAC / Checking whether added successfully? :::

1.Creating Service in Oracle RAC :

Step 1: To create a service in Oracle RAC

[oracle@rac1 ~]$ srvctl add service -d orcl -s myservice -r orcl1 -a orcl2 -P BASIC

Check service configuration :

[oracle@rac1 ~]$ srvctl config service -d orcl -s myservice

Service name: myservice

Service is enabled

Server pool: orcl_myservice

Cardinality: 1

Disconnect: false

Service role: PRIMARY

Management policy: AUTOMATIC

DTP transaction: false

AQ HA notifications: false

Failover type: NONE

Failover method: NONE

TAF failover retries: 0

TAF failover delay: 0

Connection Load Balancing Goal: LONG

Runtime Load Balancing Goal: NONE

TAF policy specification: BASIC

Edition:

Preferred instances: orcl1

Available instances: orcl1

check all available parameters of service creation :

[oracle@rac1 ~]$ srvctl add service -h

Check the status of service :

[oracle@rac1 ~]$ srvctl status service -d orcl -s myservice

Service myservice is not running.

start of service :

[oracle@rac1 ~]$ srvctl start service -d orcl -s myservice

2. Modifying service

[oracle@rac1 ~]$ srvctl modify service -d orcl -s myservice -P NONE

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

DR Sync with Incremental NO REDO Recovery Method ::::

Now we are ready to apply our differential backup to bring the standby in line with Primary:

RMAN> recover database noredo;

Because the online redo logs are lost, you must specify the NOREDO option in the RECOVER command.

You must also specify NOREDO if the online logs are available but the redo cannot be applied to the incrementals.

If you do not specify NOREDO, then RMAN searches for redo logs after applying the incremental backup, and issues an error message when it does not find them.

Use the RMAN RECOVER command with the NOREDO option to apply the incremental backup to the standby database. All changed blocks captured in the incremental backup are updated at the standby database, bringing it up to date with the primary database. With an RMAN client connected to the standby database, run the following command:

RMAN> RECOVER DATABASE NOREDO;

You can now resume managed recovery at the standby. Any redo logs required at the standby with changes since those contained in the incremental are automatically requested from the primary and applied.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Access and Filter in explain plan ::::

Kindly explain difference between "filter" and "access" listed in "Predicate Information"

and which explain the difference clearly.

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

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

| 0 | SELECT STATEMENT | | 3 | 189 | 10 (10)|

| 1 | NESTED LOOPS | | 3 | 189 | 10 (10)|

| 2 | NESTED LOOPS | | 3 | 141 | 7 (15)|

|* 3 | TABLE ACCESS FULL | EMPLOYEES | 3 | 60 | 4 (25)|

| 4 | TABLE ACCESS BY INDEX ROWID| JOBS | 19 | 513 | 2 (50)|

|* 5 | INDEX UNIQUE SCAN | JOB_ID_PK | 1 | | |

| 6 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 27 | 432 | 2 (50)|

|* 7 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | |

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

Predicate Information (identified by operation id):

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

3 - filter("E"."EMPLOYEE_ID"<103)

5 - access("E"."JOB_ID"="J"."JOB_ID")

7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"

Answers :

Access means we are using something to "access" the data - we only "access" relevant data.

Filter means we are getting more data then we need, and we filter it after we get it. The filter will be applied to all rows and only those that pass the filter get sent along.

In general - you'll see "access" when using an index for example. We are using the index to "access" only the rows of interest. You'll see filter when we don't have something to "access" only interesting rows - when we get too many rows and have to filter them.

For example:

ops$tkyte%ORA11GR2> create table t as select * from all_objects;

Table created.

ops$tkyte%ORA11GR2> exec dbms_stats.gather_table_stats( user, 'T' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2>

ops$tkyte%ORA11GR2> set autotrace traceonly explain

ops$tkyte%ORA11GR2> select * from t where object_id = 5;

Execution Plan

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

Plan hash value: 1601196873

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

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

| 0 | SELECT STATEMENT | | 1 | 97 | 291 (1)| 00:00:04 |

|* 1 | TABLE ACCESS FULL| T | 1 | 97 | 291 (1)| 00:00:04 |

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

Predicate Information (identified by operation id):

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

1 - filter("OBJECT_ID"=5)

note here that we "filtered" the data - we had to look at every row in the table - to see if any where object_id = 5, we filtered thousands of rows to find possibly one row..

Now, we add an index:

ops$tkyte%ORA11GR2> create index t_idx on t(object_id);

Index created.

ops$tkyte%ORA11GR2> select * from t where object_id = 5;

Execution Plan

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

Plan hash value: 470836197

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

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

| 0 | SELECT STATEMENT | | 1 | 97 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 97 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | T_IDX | 1 | | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("OBJECT_ID"=5)

and our filter turned into an "access", we are accessing the table by object_id using the index. We'll ONLY get rows from the table such that object_id = 5, we don't have to filter lots of rows, we just access the table by the index and get only the relevant rows.

however, you might see both in many cases:

ops$tkyte%ORA11GR2> select * from t where object_id = 5 and owner = 'SCOTT';

Execution Plan

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

Plan hash value: 470836197

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

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

| 0 | SELECT STATEMENT | | 1 | 97 | 2 (0)| 00:00:01 |

|* 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 97 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | T_IDX | 1 | | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - filter("OWNER"='SCOTT')

2 - access("OBJECT_ID"=5)

here we could "ACCESS" the object_id = 5 rows, but we had to then "FILTER" them by owner = 'SCOTT' to make sure we wanted them...

ops$tkyte%ORA11GR2> set autotrace off

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Unique Index Scan / Index scan by RowID :::

Index Unique Scans :

This scan returns, at most, a single rowid. Oracle performs a unique scan if a statement contains a UNIQUE or a PRIMARY KEY constraint that guarantees that only a single row is accessed.

An index scan is performed on the jobs and department tables, using the job_id_pk and dept_id_pk indexes, respectively.

Index scan by RowID :

If the execution plan shows a line TABLE ACCESS BY INDEX ROWID BATCHED it means that Oracle retrieves a bunch of ROWID's from the index and then

tries to access rows in block order to reduce the number of times each block needs to be accessed.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

How scan Works, changing scan Listner port and Local Listener Ports :

Steps to Modify Scan listener port number ::

Below are steps to modify the scan listener port from default.

Default Port : 1521

New Port : 1651

Step 1 : Checking scan listener configurations (run as grid user)

[oracle@node1 ]$ srvctl status listener

Listener LISTENER is enabled

Listener LISTENER is running on node(s): node2, node1

[oracle@node1 ]$ srvctl config listener

Name: LISTENER

Network: 1, Owner: oracle

Home: <CRS home>

End points: TCP:1521

[oracle@node1 ]$ srvctl status scan_listener

SCAN Listener LISTENER_SCAN1 is enabled

SCAN listener LISTENER_SCAN1 is running on node node2

SCAN Listener LISTENER_SCAN2 is enabled

SCAN listener LISTENER_SCAN2 is running on node node1

SCAN Listener LISTENER_SCAN3 is enabled

SCAN listener LISTENER_SCAN3 is running on node node1

[oracle@node1 ]$ srvctl config scan_listener

SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521

SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1521

SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1521

Check below command in Database on all nodes :-

SQL> show parameter listeners.

Step 2 : Modifying port number using srvctl as grid user

Change port number of the traditional listener :

#srvctl modify listener -l LISTENER -p 1651

Change port number of the SCAN listener :

#srvctl modify scan_listener -p TCP:1651

Note : Changes are not effective until the listeners are restarted.

Step 3 : Reload Both Listeners

# Traditional listener

srvctl stop listener

srvctl start listener

# Scan listener

srvctl stop scan_listener

srvctl start scan_listener

Verify the listeners have picked up the new port.

[oracle@node1 admin]$ srvctl config listener

Name: LISTENER

Network: 1, Owner: oracle

Home: <CRS home>

End points: TCP:1651

[oracle@node1 admin]$ srvctl config scan_listener

SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1651

SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1651

SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1651

Steps 4 : Modify remote_listener parameter

sql> alter system set remote_listener =’scan:1651′ scope=both;

Step 5 : Modify TNSNAMES.ORA files used for connectivity to reflect the new port

Modify tnsnames.ora file in Oracle database home

SLMDBA =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = scan)(PORT = 1651))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = SLMDBA)

)

Restart database :

#srvctl stop database -d SLMDBA

#srvctl start database -d SLMDBA

Verify connection to the database using sqlplus

[oracle@node1 ~]$ sqlplus system/########@SLMDBA

https://atawworkspace.yesbank.in/vpn/index.html --> reference

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Cache Fusion ::

What is Cache Fusion?

Cache Fusion Oracle RAC transfer the data block from buffer cache of one instance to the buffer cache of another instance using the cluster high speed interconnect.

For instance, Instance A read the block and it is in local buffer cache. Now Instance B wants to read the same block, then it can transfer the block(shared current image - SCUR ) from instance A buffer cache to Instance B buffer cache. It does not require additional disk read.

Instance A made changes on the particular block and it is not committed yet. Now instance B wants to read the same block, then, Instance A send the consistent read (CR) image copy to instance B. Cache Fusion avoids disk read and it greatly enhance the performance.

Let me demonstrate the cache fusion and how it works in RAC. I have three node RAC and node names are ractest1, ractest2 & ractest3. The instance names are govinddb1, govinddb2 & govinddb3. The Database version is oracle 12c. I am going to use only two nodes for this cache fusion experiment. I will create small tiny table with 6 rows and two columns and play around the cache fusion with different scenarios.

http://myorastuff.blogspot.com/2016/01/what-is-cache-fusion-and-how-it-works_21.html

https://docs.rackspace.com/blog/understanding-cache-fusion-in-rac/

Split Brain::

Split brain syndrome occurs when the instances in a RAC fails to connect or ping to each other via the private interconnect, Although the servers are physically up and running and the database instances on these servers is also running.

The individual nodes are running fine and can accept user connections and work independently.

So, in a two node situation both the instances will think that the other instance is down because of lack of connection.

The problem which could arise out of this situation is that the sane block might get read, updated in these individual instances which cause data integrity issues, because the block changed in one instance will not be locked and could be overwritten by another instance.

So, when a node fails, the failed node is prevented from accessing all the shared disk devices and groups. This methodology is called I/O Fencing, Disk Fencing or Failure Fencing

https://dba12c.wordpress.com/2015/08/26/rac-split-brain-syndrome/

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Identify Master node in RAC cluster

1. Grep occsd Log file

[oracle @ tadrac1]: /u1/app/../cssd >grep -i “master node” ocssd.log | tail -1

[CSSD]CLSS-3001: local node number 1, master node number 1

2. Grep crsd log file

[oracle @ tadrac1]: /u1/app/../crsd>grep MASTER crsd.log | tail -1

3. Query V$GES_RESOURCE view

4. ocrconfig -showbackup

The node that store OCR backups is the master node.

The role of the master node is basically that this is the node where other “clients”/nodes will contact to get information about node status… ect.

The rule is like this.

-> the first node in a cluster to be up will become the master node. -> and/or the node with the lowest number will become the master node. Information about who is the master can be found in the cssd logfile. Check for reconfiguration information at the end of the reconfiguration.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

GoldenGate Replication Flow ::

The typical GoldenGate flow shows new and changed database data being captured from the source database. The captured data is written to a file called the source trail. The trail is then read by a data pump, sent across the network, and written to a remote trail file by the Collector process. The delivery function reads the remote trail and updates the target database. Each of the components is managed by the Manager process.

GoldenGate Components

From the above flow you can see there are following GoldenGate components:-

Source Database

Capture (Local Extract) Process

Source Trail

Data Pump

Network

Collector

Remote Trail

Delivery (Replicat)

Target Database

Manager

Now we’ll understand each and every component individually:-

SOURCE DATABASE

This is basically your Oracle Database from where you want to replicate your data.

Capture (Local Extract) Process

Capture is the process of extracting data that is inserted into, updated on, or deleted from the source database. In GoldenGate, the capture process is called the Extract. In this case, the Extract is called a Local Extract (sometimes called the Primary Extract) because it captures data changes from the local source database.

Extract is an operating-system process that runs on the source server and captures changes from the database redo logs (and in some exceptional cases, the archived redo logs) and writes the data to a file called the Trail File. Extract only captures committed changes and filters out other activity such as rolled-back changes. Extract can also be configured to write the Trail File directly to a remote target server, but this usually isn’t the optimum configuration. In addition to database data manipulation language (DML) data, you can also capture data definition language (DDL) changes and sequences using Extract if properly configured. You can use Extract to capture data to initially load the target tables, but this is typically done using DBMS utilities such as export/import or Data Pump for Oracle. You can configure Extract as a single process or multiple parallel processes depending on your requirements. Each Extract process can act independently on different tables. For example, a single Extract can capture all the changes for of the tables in a schema, or you can create multiple Extracts and divide the tables among the Extracts. In some cases, you may need to create multiple parallel Extract processes to improve performance, although this usually isn’t necessary. You can stop and start each Extract process independently.

You can set up Extract to capture an entire schema using wildcarding, a single table, or a subset of rows or columns for a table. In addition, you can transform and filter captured data using the Extract to only extract data meeting certain criteria. You can instruct Extract to write any records that it’s unable to process to a discard file for later problem resolution. And you can generate reports automatically to show the Extract configuration. You can set these up to be updated periodically at user-defined intervals with the latest Extract processing statistics.

Source Trail

The Extract process sequentially writes committed transactions as they occur to a staging file that GoldenGate calls a source trail. Data is written in large blocks for high performance. Data that is written to the trail is queued for distribution to the target server or another destination to be processed by another GoldenGate process, such as a data pump. Data in the trail files can also be encrypted by the Extract and then unencrypted by the data pump or delivery process. You can size the trail files based on the expected data volume. When the specified size is reached, a

new trail file is created. To free up disk space, you can configure GoldenGate to automatically purge trail files based on age or the total number of trail files. By default, data in the trail files is stored in a platform-independent, GoldenGate proprietary format. In addition to the database data, each trail file contains a file header, and each record also contains its own header. Each of the GoldenGate processes keeps track of its position in the trail files using checkpoints, which are stored in separate files.

Data Pump

The data pump is another type of GoldenGate Extract process. The data pump reads the records in the source trail written by the Local Extract, pumps or passes them over the TCP/IP network to the target, and creates a target or remote trail. Although the data pump can be configured for data filtering and transformation (just like the Local Extract), in many cases the data pump reads the records in the source trail and simply passes all of them on as is. In GoldenGate terminology, this is called passthru mode. If data filtering or transformation is required, it’s a good idea to do this with the data pump to reduce the amount of data sent across the network.

Network

GoldenGate sends data from the source trail using the Local or data pump Extract over a TCP/IP network to a remote host and writes it to the remote trail file. The Local or data pump Extract communicates with another operating-system background Extract process called the Collector on the target. The Collector is started dynamically for each Extract process on the source that requires a network connection to the target. The Collector listens for connections on a port configured for GoldenGate. Although it can be configured, often the Collector process is ignored because it’s started dynamically and does its job without requiring changes or intervention on the target. During transmission from the source to the target, you can compress the data to reduce bandwidth.

In addition, you can tune the TCP/IP socket buffer sizes and connection timeout parameters for the best performance across the network. If needed, you can also encrypt the GoldenGate data sent across the network from the source and automatically decrypt it on the target.

Collector

The Collector process is started automatically by the Manager as needed by the Extract. The Collector process runs in the background on the target system and writes records to the remote trail. The records are sent across the TCP/IP network connection from the Extract process on the source system (either by a data pump or a Local Extract process).

Remote Trail

The remote trail is similar to the source trail, except it is created on the remote server, which could be the target database server or some other middle tier server. The source trails and the remote trails are stored in a filesystem directory named dirdat by default. They are named with a two-character prefix followed by a six-digit sequence number. The same approach for sizing for the source trail applies to the remote trail. You should size the trail files based on the expected data volume. When the specified size is reached, a new trail file will be created. You can also configure GoldenGate to automatically purge the remote trail files based on age or the total number of trail files to free up disk space. Just like the source trail, data in the remote trail files is stored in platform-independent, GoldenGate-proprietary format. Each remote trail file contains a file header, and each record also contains its own header. The GoldenGate processes keep track of its position in the remote trail files using checkpoints, which are stored in separate GoldenGate files or optionally in a database table.

Delivery (Replicat)

Delivery is the process of applying data changes to the target database. In GoldenGate, delivery is done by a process called the Replicat using the native database SQL. The Replicat applies data changes written to the trail file by the Extract process in the same order in which they were committed on the source database. This is done to maintain data integrity. In addition to replicating database DML data, you can also replicate DDL changes and sequences using the Replicat, if it’s properly configured. You can configure a special Replicat to apply data to initially load the target tables, but this is typically done using DBMS utilities such as Data Pump for Oracle. Just like the Extract, you can configure Replicat as a single process or multiple parallel processes

depending on the requirements. Each Replicat process can act independently on different tables. For example, a single Replicat can apply all the changes for all the tables in a schema, or you can create multiple Replicats and the divide the tables among them. In some cases, you may need to create multiple Replicat processes to improve performance. You can stop and start each Replicat process independently. Replicat can replicate data for an entire schema using wildcarding, a single table, or a subset of rows or columns for a table. You can configure the Replicat to map the data from the source to the target database, transform it, and filter it to only replicate data meeting certain criteria. You can write any records that Replicat is unable to process to a discard file for problem resolution. Reports can be automatically generated to show the Replicat configuration; these reports can be updated periodically at user-defined intervals with the latest processing statistics.

Target Database

This is basically the Oracle Database where you want the changes to be replicated.

Manager

The GoldenGate Manager process is used to manage all of the GoldenGate processes and resources. A single Manager process runs on each server where GoldenGate is executing and processes commands from the GoldenGate Software Command Interface (GGSCI). The Manager process is the first GoldenGate process started. The Manager then starts and stops each of the other GoldenGate processes, manages the trail files, and produces log files and reports.

https://databaseinternalmechanism.com/oracle-goldengate/architecture/

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Using the Discard File

By default, a discard file is generated whenever a process is started with the START command through GGSCI. The discard file captures information about Oracle GoldenGate operations that failed. This information can help you resolve data errors, such as those that involve invalid column mapping.

The discard file reports such information as:

The database error message

The sequence number of the data source or trail file

The relative byte address of the record in the data source or trail file

The details of the discarded operation, such as column values of a DML statement or the text of a DDL statement.

To view the discard file, use a text editor or use the VIEW REPORT command in GGSCI.

The default discard file has the following properties:

The file is named after the process that creates it, with a default extension of .dsc. Example: finance.dsc.

The file is created in the dirrpt sub-directory of the Oracle GoldenGate installation directory.

The maximum file size is 50 megabytes.

At startup, if a discard file exists, it is purged before new data is written.

You can change these properties by using the DISCARDFILE parameter. You can disable the use of a discard file by using the NODISCARDFILE parameter.

If a proces is started from the command line of the operating system, it does not generate a discard file by default. You can use the DISCARDFILE parameter to specify the use of a discard file and its properties.

Once created, a discard file must r need to emain in its original location for Oracle GoldenGate to operate properly after processing has started.

+++++++++++part2 need to modify

Questions links : https://slmdba.blogspot.com/2021/11/interview-questions-which-are.html

Answer Details :

Below answers which are collected from different sites and blogs for preparations point of only:

Below details having any queries please comment on below and also share which is better than our information will update the same.

RAC Node Eviction Types (NHB/DHB) ::::

http://oracle-help.com/oracle-rac/node-eviction-oracle-rac/

Two types :

1.css misscount For Network heartbeat

2.disk misscount for Disk heartbeat

Default value for css misscount is 30 seconds.

To check value of this parameter

[oracle@db02 ~]$ crsctl get css misscount

CRS-4678: Successful get misscount 60 for Cluster Synchronization Services.

Default value 3 seconds

You can check value of reboottime parameter by

[oracle@db02 ~]$ crsctl get css reboottime

CRS-4678: Successful get reboottime 3 for Cluster Synchronization Services.

Default value is 200 seconds

To check the value for disktimeout parameter :

[oracle@db02 ~]$ crsctl get css disktimeout

CRS-4678: Successful get disktimeout 200 for Cluster Synchronization Services.

With 11gR2, these settings can be changed online without taking any node down:

1) Execute crsctl as root to modify the misscount:

$CRS_HOME/bin/crsctl set css misscount n

$CRS_HOME/bin/crsctl set css reboottime n

$CRS_HOME/bin/crsctl set css disktimeout n

1.css misscount For Network heartbeat

Cause : Network failure (e.g. packet errors/retransmits) or Network latency (e.g. packet snd/rcv high ms with retransmits) between nodes,

heartbeats are issued once per sec.

Note :

•If the OS messages file reboot timestamp < missed checkin timestamp then the node eviction was likely not due to these missed checkins.

2.disk misscount for Disk heartbeat

Cuause :Majority of the voting disks are unavailable or I/O latency, heartbeats are issued once per sec.

Note

•If strictly more than half of the voting disks are up and contain consistent information, the CSS can use that consistent data.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Add Service in RAC / Checking whether added successfully? :::

http://oracle-help.com/oracle-rac/creating-deleting-modifying-services-oracle-rac/

1.Creating Service in Oracle RAC :

Step 1: To create a service in Oracle RAC

[oracle@rac1 ~]$ srvctl add service -d orcl -s myservice -r orcl1 -a orcl2 -P BASIC

Check service configuration :

[oracle@rac1 ~]$ srvctl config service -d orcl -s myservice

Service name: myservice

Service is enabled

Server pool: orcl_myservice

Cardinality: 1

Disconnect: false

Service role: PRIMARY

Management policy: AUTOMATIC

DTP transaction: false

AQ HA notifications: false

Failover type: NONE

Failover method: NONE

TAF failover retries: 0

TAF failover delay: 0

Connection Load Balancing Goal: LONG

Runtime Load Balancing Goal: NONE

TAF policy specification: BASIC

Edition:

Preferred instances: orcl1

Available instances: orcl1

check all available parameters of service creation :

[oracle@rac1 ~]$ srvctl add service -h

Check the status of service :

[oracle@rac1 ~]$ srvctl status service -d orcl -s myservice

Service myservice is not running.

start of service :

[oracle@rac1 ~]$ srvctl start service -d orcl -s myservice

2. Modifying service

[oracle@rac1 ~]$ srvctl modify service -d orcl -s myservice -P NONE

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

DR Sync with Incremental NO REDO Recovery Method ::::

https://jhdba.wordpress.com/tag/rman-noredo/

Now we are ready to apply our differential backup to bring the standby in line with Primary:

RMAN> recover database noredo;

Because the online redo logs are lost, you must specify the NOREDO option in the RECOVER command.

You must also specify NOREDO if the online logs are available but the redo cannot be applied to the incrementals.

If you do not specify NOREDO, then RMAN searches for redo logs after applying the incremental backup, and issues an error message when it does not find them.

https://web.stanford.edu/dept/itss/docs/oracle/10gR2/backup.102/b14191/rcmdupdb008.htm

RMAN> RECOVER DATABASE NOREDO;

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Access and Filter in explain plan ::::

https://asktom.oracle.com/pls/apex/f?p=100:11:::NO:RP:P11_QUESTION_ID:7807480400346035212

Kindly explain difference between "filter" and "access" listed in "Predicate Information"

and which explain the difference clearly.

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

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

| 0 | SELECT STATEMENT | | 3 | 189 | 10 (10)|

| 1 | NESTED LOOPS | | 3 | 189 | 10 (10)|

| 2 | NESTED LOOPS | | 3 | 141 | 7 (15)|

|* 3 | TABLE ACCESS FULL | EMPLOYEES | 3 | 60 | 4 (25)|

| 4 | TABLE ACCESS BY INDEX ROWID| JOBS | 19 | 513 | 2 (50)|

|* 5 | INDEX UNIQUE SCAN | JOB_ID_PK | 1 | | |

| 6 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 27 | 432 | 2 (50)|

|* 7 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | |

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

Predicate Information (identified by operation id):

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

3 - filter("E"."EMPLOYEE_ID"<103)

5 - access("E"."JOB_ID"="J"."JOB_ID")

7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"

Answers :

Access means we are using something to "access" the data - we only "access" relevant data.

Filter means we are getting more data then we need, and we filter it after we get it. The filter will be applied to all rows and only those that pass the filter get sent along.

For example:

ops$tkyte%ORA11GR2> create table t as select * from all_objects;

Table created.

ops$tkyte%ORA11GR2> exec dbms_stats.gather_table_stats( user, 'T' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2>

ops$tkyte%ORA11GR2> set autotrace traceonly explain

ops$tkyte%ORA11GR2> select * from t where object_id = 5;

Execution Plan

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

Plan hash value: 1601196873

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

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

| 0 | SELECT STATEMENT | | 1 | 97 | 291 (1)| 00:00:04 |

|* 1 | TABLE ACCESS FULL| T | 1 | 97 | 291 (1)| 00:00:04 |

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

Predicate Information (identified by operation id):

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

1 - filter("OBJECT_ID"=5)

note here that we "filtered" the data - we had to look at every row in the table - to see if any where object_id = 5, we filtered thousands of rows to find possibly one row..

Now, we add an index:

ops$tkyte%ORA11GR2> create index t_idx on t(object_id);

Index created.

ops$tkyte%ORA11GR2> select * from t where object_id = 5;

Execution Plan

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

Plan hash value: 470836197

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

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

| 0 | SELECT STATEMENT | | 1 | 97 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 97 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | T_IDX | 1 | | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("OBJECT_ID"=5)

however, you might see both in many cases:

ops$tkyte%ORA11GR2> select * from t where object_id = 5 and owner = 'SCOTT';

Execution Plan

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

Plan hash value: 470836197

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

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

| 0 | SELECT STATEMENT | | 1 | 97 | 2 (0)| 00:00:01 |

|* 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 97 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | T_IDX | 1 | | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - filter("OWNER"='SCOTT')

2 - access("OBJECT_ID"=5)

here we could "ACCESS" the object_id = 5 rows, but we had to then "FILTER" them by owner = 'SCOTT' to make sure we wanted them...

ops$tkyte%ORA11GR2> set autotrace off

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Unique Index Scan / Index scan by RowID :::

Index Unique Scans :

This scan returns, at most, a single rowid. Oracle performs a unique scan if a statement contains a UNIQUE or a PRIMARY KEY constraint that guarantees that only a single row is accessed.

An index scan is performed on the jobs and department tables, using the job_id_pk and dept_id_pk indexes, respectively.

Index scan by RowID :

If the execution plan shows a line TABLE ACCESS BY INDEX ROWID BATCHED it means that Oracle retrieves a bunch of ROWID's from the index and then

tries to access rows in block order to reduce the number of times each block needs to be accessed.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

How scan Works, changing scan Listner port and Local Listener Ports :

Steps to Modify Scan listener port number ::

Below are steps to modify the scan listener port from default.

Default Port : 1521

New Port : 1651

Step 1 : Checking scan listener configurations (run as grid user)

[oracle@node1 ]$ srvctl status listener

Listener LISTENER is enabled

Listener LISTENER is running on node(s): node2, node1

[oracle@node1 ]$ srvctl config listener

Name: LISTENER

Network: 1, Owner: oracle

Home: <CRS home>

End points: TCP:1521

[oracle@node1 ]$ srvctl status scan_listener

SCAN Listener LISTENER_SCAN1 is enabled

SCAN listener LISTENER_SCAN1 is running on node node2

SCAN Listener LISTENER_SCAN2 is enabled

SCAN listener LISTENER_SCAN2 is running on node node1

SCAN Listener LISTENER_SCAN3 is enabled

SCAN listener LISTENER_SCAN3 is running on node node1

[oracle@node1 ]$ srvctl config scan_listener

SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521

SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1521

SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1521

Check below command in Database on all nodes :-

SQL> show parameter listeners.

Step 2 : Modifying port number using srvctl as grid user

Change port number of the traditional listener :

#srvctl modify listener -l LISTENER -p 1651

Change port number of the SCAN listener :

#srvctl modify scan_listener -p TCP:1651

Note : Changes are not effective until the listeners are restarted.

Step 3 : Reload Both Listeners

# Traditional listener

srvctl stop listener

srvctl start listener

# Scan listener

srvctl stop scan_listener

srvctl start scan_listener

Verify the listeners have picked up the new port.

[oracle@node1 admin]$ srvctl config listener

Name: LISTENER

Network: 1, Owner: oracle

Home: <CRS home>

End points: TCP:1651

[oracle@node1 admin]$ srvctl config scan_listener

SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1651

SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1651

SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1651

Steps 4 : Modify remote_listener parameter

sql> alter system set remote_listener =’scan:1651′ scope=both;

Step 5 : Modify TNSNAMES.ORA files used for connectivity to reflect the new port

Modify tnsnames.ora file in Oracle database home

SLMDBA =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = scan)(PORT = 1651))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = SLMDBA)

)

Restart database :

#srvctl stop database -d SLMDBA

#srvctl start database -d SLMDBA

Verify connection to the database using sqlplus

[oracle@node1 ~]$ sqlplus system/########@SLMDBA

https://atawworkspace.yesbank.in/vpn/index.html --> reference

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Cache Fusion ::

What is Cache Fusion?

Cache Fusion Oracle RAC transfer the data block from buffer cache of one instance to the buffer cache of another instance using the cluster high speed interconnect.

http://myorastuff.blogspot.com/2016/01/what-is-cache-fusion-and-how-it-works_21.html

https://docs.rackspace.com/blog/understanding-cache-fusion-in-rac/

Split Brain::

The individual nodes are running fine and can accept user connections and work independently.

So, in a two node situation both the instances will think that the other instance is down because of lack of connection.

So, when a node fails, the failed node is prevented from accessing all the shared disk devices and groups. This methodology is called I/O Fencing, Disk Fencing or Failure Fencing

https://dba12c.wordpress.com/2015/08/26/rac-split-brain-syndrome/

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Identify Master node in RAC cluster

1. Grep occsd Log file

[oracle @ tadrac1]: /u1/app/../cssd >grep -i “master node” ocssd.log | tail -1

[CSSD]CLSS-3001: local node number 1, master node number 1

2. Grep crsd log file

[oracle @ tadrac1]: /u1/app/../crsd>grep MASTER crsd.log | tail -1

3. Query V$GES_RESOURCE view

4. ocrconfig -showbackup

The node that store OCR backups is the master node.

The role of the master node is basically that this is the node where other “clients”/nodes will contact to get information about node status… ect.

The rule is like this.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

GoldenGate Replication Flow ::

GlodenGate Replication Flow

GoldenGate Components

From the above flow you can see there are following GoldenGate components:-

Source Database

Capture (Local Extract) Process

Source Trail

Data Pump

Network

Collector

Remote Trail

Delivery (Replicat)

Target Database

Manager

Now we’ll understand each and every component individually:-

SOURCE DATABASE

This is basically your Oracle Database from where you want to replicate your data.

Capture (Local Extract) Process

Source Trail

Data Pump

Network

Collector

Remote Trail

Delivery (Replicat)

Target Database

This is basically the Oracle Database where you want the changes to be replicated.

Manager

https://databaseinternalmechanism.com/oracle-goldengate/architecture/

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Using the Discard File

The discard file reports such information as:

The database error message

The sequence number of the data source or trail file

The relative byte address of the record in the data source or trail file

The details of the discarded operation, such as column values of a DML statement or the text of a DDL statement.

To view the discard file, use a text editor or use the VIEW REPORT command in GGSCI.

The default discard file has the following properties:

The file is named after the process that creates it, with a default extension of .dsc. Example: finance.dsc.

The file is created in the dirrpt sub-directory of the Oracle GoldenGate installation directory.

The maximum file size is 50 megabytes.

At startup, if a discard file exists, it is purged before new data is written.

You can change these properties by using the DISCARDFILE parameter. You can disable the use of a discard file by using the NODISCARDFILE parameter.

Once created, a discard file must remain in its original location for Oracle GoldenGate to operate properly after processing has started.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Restore of OCR,VD and ASM password file :

Restore the OCR from backup done before.

— root account

$ ocrconfig -restore /backup/OCR/ocr.bkp

$ ocrcheck

https://eclipsys.ca/oracle-rac-12c-a-recipe-to-recover-from-losing-ocr-voting-disk-or-asm-password/

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Oracle RAC (11g / 12c) wait events :

gc current block 2-way => Blocks are busy in another instance, check for block level contention or hot blocks.

gc cr block 2-way => Blocks are busy in another instance, check for block level contention or hot blocks.

gc current block 3-way => Blocks are busy in another instance, check for block level contention or hot blocks.

gc cr block 3-way => Blocks are busy in another instance, check for block level contention or hot blocks.

gc current block busy => The current block read request was delayed, most likely an I/O bottleneck. Block is already involved in GC operation, shows hot blocks or congestion.

gc cr block busy => The consistent read request was delayed, most likely an I/O bottleneck. Blocks are busy in another instance, check for block level contention or hot blocks.

gc current block congested => Long run queues and/or paging due to memory deficiency. Current block congestion, check for hot blocks or busy interconnect.

gc cr block congested => Long run queues and/or paging due to memory deficiency. cr block congestion, check for hot blocks or busy interconnect.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Datafiles contains uncommitted data

We are aware that LGWR writes both committed and uncommitted data from redo log buffer to redo log files not only when we commit but also when the log buffer

is 10MB full, 1/3 full , every 3 seconds or every commit –whichever is first.

But nowadays, DBWR also does continuous checkpointing and flush the dirty buffers to disk (i.e., writes both committed and uncommitted data to datafiles) because of the following reasons.

a. When we do transactions greater than the available memory, we need some free buffers for transactions to complete.

b. When DBWR flush dirty blocks to disk, redo logs will have enough space and can be reused.

c. Limits the time to recovery after a crash using the parameter FAST_START_MTTR_TARGET.

Consider, we are doing a bulk update.

Then commit.

Next, the system suddenly crashes ,

If we have all data left in the cache by DBWR, then while recovery, all the data needs to be reapplied and would take long time.

DBWR flush dirty buffers to datafiles when one of the following occurs.

a. Checkpoint occurs

b. Dirty buffers reach threshold / Flush buffer cache

c. No free buffers

d. Before logfile gets overwritten/Before log switch

e. Tablespace offline

f. Tablespace read only

g. Table drop or truncate

h. Tablespace begin backup

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Huges pages:

Huge Pagehttps://www.carajandb.com/en/blog/2016/7-easy-steps-to-configure-hugepages-for-your-oracle-database-server/

Data Base Administrative

No comments:

Post a Comment