Tuning dataguard and RMAN

Session Data Unit

As we deal with wide area network (WAN) connectivity between the primary database and the standby database, we often have the need to adjust the session data unit (SDU) to gain additional performance and throughput. We will want to adjust the SDU when the transferred data is fragmented into separate packets or large amounts of data are being transferred.

/u01/app/oracle/product/11.2.0.3/dbhome_1/network/admin/sqlnet.ora

# -- This will set SDU to 32k for all the databases
# --
DEFAULT_SDU_SIZE=32767
TCP.NODELAY=YES

Block Change Tracking

If you are performing any level of incremental backups, you need to enable block change tracking (BCT) to track changed blocks on the database since the last backup. Enabling BCT will significantly reduce your backup window for incremental backups as RMAN leverages the BCT file to identify specific blocks that must be backed up

alter database enable block change tracking using file '+DATA_EXAP';

SQL> select filename, status, bytes from v$block_change_tracking;

image copy of the database again. Updated incremental backup image copies are essential when it comes to reducing restore times, deploying snapshot technology, and creating clones of production snapshot data.

Settings for RMAN Backups on the Exadata

With RMAN, we can perform image copy backups or backup set backups. More companies choose RMAN backup set implementations simply because this technology is what they are comfortable with. Instead, consider image copy backups with incremental updates on the Exadata and imagine a world where you do not have to perform a full backup again. Depending on which method we choose, we can tune the RMAN backup processes with the following hidden underscore (_) parameters:

 _backup_disk_bufcnt—number of buffers used to process backup sets

 _backup_disk_bufsz—size of the buffers used to process backup sets

 _backup_file_bufcnt—number of buffers used to process image copies

 _backup_file_bufsz—size of the buffers used to process image copies

Table 8.1 depicts the best-practice recommendations for the number of buffers and buffer size to perform backup sets and image copies for RMAN backups over IB or 10GigE connections to the Exadata over dNFS.

Table 8.1 RMAN Backup Settings

The following are additional recommended guidelines:

 Use two to four RMAN channels per tray of disks.

 Load-balance RMAN channels across shares and controllers.

 Load-balance RMAN channels across Exadata Compute Nodes.

Suppressing indexes unintentionally

Unintentionally suppressing indexes is one of the most common mistakes made by an inexperienced developer. SQL contains many traps that cause indexes not to be used.

• Using the NOT EQUAL Operators: <>, !=
  Indexes can only be used to find data that exists within a table. Whenever the NOT EQUAL operators are used in the WHERE clause, indexes on the columns being referenced cannot be used.
  
• Using IS NULL or IS NOT NULL
  
  When you use IS NULL or IS NOT NULL in your WHERE clauses, index usage is suppressed because the value of NULL is undefined

• Using LIKE
  
  LIKE, in some cases, uses an index, while in others, it does not. The most common uses of LIKE are LIKE ‘%somevalue%’ or LIKE ‘somevalue%’ (where the % is only at the end of the search string). Only one of these cases uses the index—the case where the value is first, LIKE ‘somevalue%’.
   
•  Using Function
  
  Unless you are using function-based indexes, applying functions on indexed columns in the WHERE clause of a SQL statement causes the optimizer to bypass indexes. Some of the most common functions are TRUNC, SUBSTR, TO_DATE, TO_CHAR, and INSTR

Finding RAC Wait Events—Sessions Waiting

To identify the sessions experiencing waits on the system, perform the following tasks:

1.   Query GV$SESSION_WAIT to determine whether any sessions are experiencing RAC-related waits (at the current time).

2.   Identify the objects that are causing contention for these sessions.

3.   Try to modify the object or query to reduce contention.

For example, query GV$SESSION_WAIT (shown next) to determine whether any sessions are experiencing RAC cache–related waits. Note that the GV$ views are used much more to show statistics for the entire cluster, whereas the V$ views still show statistics from a single node. If you plan to use RAC, you must extend the V$ views and queries to the GV$ views for multiple nodes. This section is only an initial guide to help you see all of the components. The scope of this book does not cover RAC specifically, but some things that will help you tune RAC.

The output from this query should look something like this:

Run this query to identify objects that are causing contention for these sessions and the object that corresponds to the file and block for each FILE_NUMBER/BLOCK_NUMBER combination returned (this query is a bit slower):

The output is similar to

Modify the object to reduce the chances for application contention by doing the following:

   Reduce the number of rows per block.

   Adjust the block size to a smaller block size.

   Modify INITRANS and FREELISTS.

Oracle Interview Questions - Part 1 (database)

What is the difference between SYSDBA, SYSOPER and SYSASM?
SYSOPER can’t create and drop database.
SYSOPER can’t do incomplete recovery.
SYSOPER can’t change character set.
SYSOPER can’t CREATE DISKGROUP, ADD/DROP/RESIZE DISK

SYSASM can do anything SYSDBA can do.

What is the difference between SYS and SYSTEM?
SYSTEM can’t shutdown the database.
SYSTEM can’t create another SYSTEM, but SYS can create another SYS or SYSTEM.

21. What is a datafile?
Every Oracle database has one or more physical datafiles. Datafiles contain all the database data. The data of logical database structures such as tables and indexes is physically stored in the datafiles allocated for a database.

22. What are the contents of control file?
Database name, SCN, LSN, datafile locations, redolog locations, archive mode, DB Creation Time, RMAN Backup & Recovery Details, Flashback mode.

1. What is direct path read?
A direct path read is where the data is read directly from the data files into the PGA rather than into the buffer cache in the SGA. The direct path read is available only when the Oracle optimizer chooses a full table scan.


3. What is cache buffer chains wait event ?
A user process acquires the CACHE BUFFERS CHAINS latch to scan the System Global Area (SGA) for database cache buffers. Blocks in the buffer cache are placed on linked lists (cache buffer chains). Blocks are put on the hash chain according to their DBA (data block address) and CLASS of the block. Each hash chain is protected by a single child latch. The latch allows a process to scan a hash chain without having the linked list change while it scans.
Hot blocks are common cause of cache buffers chains latch contention.

4. What are Scattered read and sequential read ?
A db file sequential read is an event that shows a wait for a foreground process while doing a sequential read from the database

A db file scattered read will read multiple data blocks. Multi-block reads are typically used on full table scans.

5. What is the difference between Hash join and nested loop join?
hash join uses a hash-table based lookup mechanism while nested loop doesn't or that the hash join can use cursor work-area memory (allocated in UGA) for buffering rows, while nested loops join can not.

6. What will happens when we open database in resetlogs ?
Use RESETLOGS after incomplete recovery (when the entire redo stream wasn't applied). RESETLOGS will initialize the logs, reset your log sequence number, and start a new "incarnation" of the database.

7. What is cursor sharing ?
A cursor is a name to a specific private SQL area. The cursor contains session-specific state information such as bind variable values and result sets. you can think of a cursor as a pointer on the client side and as a state on the server side.



9. What is bind peeking?
One of the first drawbacks of bind variables are the explain plan.  Oracle optimizer is able to peek the value of the bind variable and generate a plan like if the query where with a fixed value and so using histograms and statistics.
Bind variable peeking is used with SQL statements in the library cache to see if the value of a host variable is going to change the existing execution plan.

10. What is adaptive cursor sharing ?
Oracle 11g uses Adaptive Cursor Sharing to solve bind peeking problem by allowing the server to compare the effectiveness of execution plans between executions with different bind variable values.


14. What are histograms?
Histograms are a feature of the cost-based optimizer (CBO) that allows the Oracle engine to determine how data is distributed within a column. They are most useful for a column that is included in the WHERE clause of SQL and the data distribution is skewed.
Histograms provide improved selectivity estimates in the presence of data skew, resulting in optimal execution plans with non-uniform data distributions.



75. What is the difference between instance recovery and crash recovery ?
Crash recovery is done automatically by SMON, in case of abonormal shutdown such as shutdown abort or any such reason.

Instance recovery refers to the case where a surviving instance recovers a failed instance in an Oracle Real Application Clusters (RAC) database.

Oracle Interview questions - Part 2 (RAC)

What is a VIP in RAC use for?
The VIP is an alternate Virtual IP address assigned to each node in a cluster. During a node failure the VIP of the failed node moves to the surviving node and relays to the application that the node has gone down. Without VIP, the application will wait for TCP timeout and then find out that the session is no longer live due to the failure.


How do we know which database instances are part of a RAC cluster?
You can query the V$ACTIVE_INSTANCES view to determine the member instances of the RAC cluster


What is a rolling upgrade?
A patch is considered a rolling if it is can be applied to the cluster binaries without having to shutting down the database in a RAC environment


What is cache fusion?
Ans:
Transferring of data between RAC instances by using private network. Cache Fusion is the remote memory mapping of Oracle buffers, shared between the caches of participating nodes in the cluster


What is the purpose of Private Interconnect?
Ans:
Clusterware uses the private interconnect for cluster synchronization (network heartbeat) and daemon communication between the clustered node


What is OCR file?
Ans:
RAC configuration information repository that manages information about the cluster node list and instance-to-node mapping information.


How do you identify the OCR file location?
Ans:
check /var/opt/oracle/ocr.loc or /etc/ocr.loc
# ocrcheck


What is Voting file/disk and how many files should be there?
Voting disk is akin to the quorum disk, which helps to avoid the split-brain syndrome. Oracle RAC uses the voting disk to manage cluster membership by way of a health check and arbitrates cluster ownership among the instances in case of network failures
Number of voting files must be odd i.e. 1, 3, 5, 7, 9


How do I identify the voting disk location?
Ans:
# crsctl query css votedisk


What is GNS?
Grid Naming service is alternative service to DNS , which will act as a sub domain in your DNS but managed by Oracle, with GNS the connection is routed to the cluster IP and manages internally

What is FAN?
Ans:
Applications can use Fast Application Notification (FAN) to enable rapid failure detection,


What is TAF and TAF policies?
Ans:
Transparent Application Failover (TAF) - A runtime failover for high availability environments, such as Real Application Clusters and Oracle Real Application Clusters Guard, TAF refers to the failover and re-establishment of application-to-service connections. It enables client applications to automatically reconnect to the database if the connection fails


How to know the public IPs, private IPs, VIPs in RAC?
Ans:
# olsnodes -n -p -i
Client Side Connect-Time Load Balance:
 The client load balancing feature enables clients to randomize connection requests among the listeners.
 This is done by client Tnsnames Parameter: LOAD_BALANCE.
 The (load_balance=yes) instructs SQLNet
Server Side Listener Connection Load Balancing.

With server-side load balancing, the listener directs a connection request to the best instance currently providing the service.
 Init parameter remote_listener should be set.
===================================================

Basic admin for Oracle 12c MultiTenant

Connecting to PDBs

SELECT name, pdb FROM   v$services ORDER BY name;

Display current container

SQL> SHOW CON_NAME

Switching Between Containers

SQL> ALTER SESSION SET CONTAINER=pdb1;

SQL> ALTER SESSION SET CONTAINER=cdb$root;

Startup/shutdown

SELECT name, open_mode FROM v$pdbs;

ALTER PLUGGABLE DATABASE pdb1, pdb2 OPEN READ ONLY FORCE;
ALTER PLUGGABLE DATABASE pdb1, pdb2 CLOSE IMMEDIATE;

ALTER PLUGGABLE DATABASE ALL OPEN;
ALTER PLUGGABLE DATABASE ALL CLOSE IMMEDIATE;

ALTER PLUGGABLE DATABASE ALL EXCEPT pdb1 OPEN;
ALTER PLUGGABLE DATABASE ALL EXCEPT pdb1 CLOSE IMMEDIATE;

Preserve PDB Startup State

ALTER PLUGGABLE DATABASE pdb1 OPEN;
ALTER PLUGGABLE DATABASE pdb1 SAVE STATE;

SELECT con_name, instance_name, state FROM dba_pdb_saved_states;

Managing Users

Multitenant environments there are two types of user.

• Common User : The user is present in all containers (root and all PDBs).
• Local User : The user is only present in a specific PDB. The same username can be present in multiple PDBs, but they are unrelated.

Create common users
CONN / AS SYSDBA

-- Create the common user using the CONTAINER clause.
CREATE USER c##test_user1 IDENTIFIED BY password1 CONTAINER=ALL;
GRANT CREATE SESSION TO c##test_user1 CONTAINER=ALL;


Create local users
CONN / AS SYSDBA

-- Switch container while connected to a common user.
ALTER SESSION SET CONTAINER = pdb1;

-- Create the local user using the CONTAINER clause.
CREATE USER test_user3 IDENTIFIED BY password1 CONTAINER=CURRENT;
GRANT CREATE SESSION TO test_user3 CONTAINER=CURRENT;

Tracking PDBs to Datafiles

Map tables to PDBs:

Checking PDB History

ORATOP - Top process for Oracle databases

Key	description
t	display tablespace information.
a	displays the database's ASM information
s	toggle the fourth section between showing session information, the default, and showing SQL statements
x	lets the database administrator see the Explain Plan for a given SQL identifier.
h	help
m	Module/process
k	File#, block#
f	Long format
i	Interval display
d	Real time processing

RED

Values flagged and highlighted in red color are merely a warning or to emphasize importance

MySQL - How to perform basic admin

MySQL - How to perform basic admin

How to display output

display in vertical : -E, --vertical

 

variables : --print-defaults

 

save output : -tee= save html : -H

 

How to check MySQL server is alive?

alive : mysqladmin -p ping

 

How to Find out current Status of MySQL server?

mysqladmin -u root -p extended-status mysqladmin -u root -p status

How to check MySQL version?

mysqladmin -u root -p version

How to stop MYSQL?

mysqladmin -p shutdown

How to set root password?

mysqladmin -u root password YOURNEWPASSWORD

How to check all the running Process of MySQL server?

mysqladmin -u root -p processlist

How to kill a process

>

mysql> SHOW PROCESSLIST; or mysql> SELECT * FROM information_schema.processlist ORDER BY id; mysql> Kill thread_id or mysqladmin kill id

How to create a job

mysql > CREATE EVENT IF NOT EXISTS test_event_01 ON SCHEDULE AT CURRENT_TIMESTAMP DO … mysql> SHOW EVENTS FROM …

http://www.mysqltutorial.org/mysql-triggers/working-mysql-scheduled-event/

MySQL Backup and Recovery

This article provides a quick guide to performing backup and recovery of MySQL databases

Logical Backup (mysqldump)

Backup database

mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql

Backup multiple databases

mysqldump -u root -ptmppassword --databases bugs sugarcrm > bugs_sugarcrm.sql

 

Restore a database


In this example, to restore the sugarcrm database, execute mysql with < as shown below. When you are restoring the dumpfilename.sql on a remote database, make sure to create the sugarcrm database before you can perform the restore

mysql -u root -ptmppassword mysql> create database sugarcrm; mysql -u root -ptmppassword sugarcrm < /tmp/sugarcrm.sql mysql -u root -p[root_password] [database_name] < dumpfilename.sql

Exadata - tools for administration

Purpose	Command
Conduct a comprehensive Exadata health check on your Exadata Database Machine to validate your hardware, firmware,	./exachk -a
Collecting RAID Storage Information	/opt/MegaRAID/MegaCli/MegaCli64 -PDList –aALL
Administering the Storage Cell Network	ipconf –verify
Diagnosing Your InfiniBand Network	/usr/bin/ibdiagnet /usr/sbin/ibqueryerrors.pl
InfiniBand network performance	opt/oracle.SupportTools/ibdiagtools/infinicheck
Exadata version	imageinfo imagehistory
oswatcher	exawatcher

 

Exadata - the key software components and their location

Directory/Executable/File	Purpose
/opt/oracle	Top level directory containing Oracle storage server software
/opt/oracle.cellos	Directory containing Exadata cell software and utilities
/opt/oracle.cellos/cell.conf	Cell configuration file
/opt/oracle.cellos/CheckHWnFWProfile	Utility to validate hardware profile
/opt/oracle.cellos/ExadataDiagCollector.sh	Utility to collect cell diagnostics data, valuable for SRs
/opt/oracle.cellos/functions_cellos	Contains various Cell OS function calls
/opt/oracle.cellos/imageinfo	Shows current image information
/opt/oracle.cellos/imagehistory	Shows image history
/opt/oracle.cellos/ipconf[.pl]	Displays or configures cell network environment
/opt/oracle.cellos/iso	Contains kernel ISO images
/opt/oracle.cellos/make_cellboot_usb.sh	Creates a USB rescue image
/opt/oracle.cellos/MegaCli64	MegaCLI—also in /opt/MegaRAID/MegaCli
/opt/oracle.cellos/patch	Directory for staged patches
/opt/oracle.cellos/restore_cellboot.sh	Restores from USB rescue image
/opt/oracle.cellos/validations_cell	Directory containing output from cell server validations
/opt/oracle.cellos/vldconfig	Configures cell validation
/opt/oracle.cellos/vldrun	Runs cell validation scripts and logs to /opt/oracle.cellos/validations_cellw
/opt/oracle/cell	Symlink to /opt/oracle.cell[VERSION]
/opt/oracle/cell[VERSION]	Directory containing current cell software. For example, /opt/oracle/cell11.2.2.4.2_LINUX.X64_111221
/opt/oracle/cell[VERSION]/cellsrv	Directory containing cellsrv software
/opt/oracle/cell[VERSION]/cellsrv/deploy/config	Configuration deployment files for active cell image
/opt/oracle/cell[VERSION]/cellsrv/deploy/config/cellinit.ora	Cell initialization parameter file
/var/log/oracle	Directory containing cell server log files, alerts, and trace files
/var/log/oracle/cellos	Directory containing log and trace files for Cell Services utilities, validation framework, and cell server startup/shutdown events
/var/log/oracle/diag/asm	Directory containing log and trace files for cell storage-related events in your cell

Hardware sizing for Oracle database

1. Dell white paper for Oracle database sizing

5 key features that empowers Oracle Exadata

5 key features that empowers this engineered system:

1. Smart Flash Cache
The Exadata Storage Server layer includes some flash storage as a hardware component, which has been implemented as a set of PCI flash cards. The main benefit of that is of course faster access than standard, disk-based access. In some cases you can order Exadata to KEEP whole table in Smart Flash Cache which speeds up database layer access with Full Table Scans. On the other hand in Write-Back mode you can empower DBWR or LGWR performance by putting written data on flash cache first, and afterwards sync it with regular hard drives, which of course is transparent to database engine.


2. Storage Indexes
It is absolutely unique to Exadata. Storage Indexes are an intelligent storage implementation. Generally speaking classic database index by it definition is created to efficiently provide a location of a certain data key. To be honest Storage Indexes in Exadata are focused on eliminating areas on storage as possible place where data might exist. Online data maps which are completely transparent for database layer are stored on the Flash Cache of Exadata Storage Servers. To make the story short, when Exadata Storage Server scans through the Storage Index and identifies the regions where predicate value falls within the MIN/MAX for the region, only for the identified regions physical I/O occurs. And as a consequence, even Full Table Scan has been planned by CBO on Database Server layer, limited I/O operations use to be proceed.


3. Smart Scans and Cell Offloading
Offloading use to be called as secret sauce of Oracle Exadata. The main concept of Offloading is to move processing from DB Nodes (Database Servers) to the intelligent storage layer. What is even more important Offloading means the reduction in the volume of data that returns to database server, which is one of the major bottlenecks in terabytes or even more bigger databases. To eliminate the time spent on transferring completely unnecessary data between storage and the database tier is the main issue that Oracle Exadata has been built to solve. Keep in mind that Offloading and Smart Scan terms could be used somewhat interchangeably.


4. Hybrid Columnar Compression
Also known as HCC, Hybrid Columnar Compression is one of the key features of Oracle Exadata, and it is only available on this engineered system. HCC format of compression will be used only when data arrives with direct path loads. There are four levels of compression QUERY LOW (LZO, 4x), QUERY HIGH (ZLIB, 6x), ARCHIVE LOW (ZLIB, 7x) and ARCHIVE HIGH (Bzip2, 12x). Keep in mind that HCC is not a good option for OLTP systems. In case of HCC mechanics, HCC store data in nontraditional format. Even data resides in Oracle blocks, with block header for every block, in HCC data storage has been organized in logical structures called compression units (CUs). Each CU consists of multiple Oracle blocks.


5. IORM
I/O Resource Manager (IORM) is a Oracle Exadata features which enriches Oracle Resource Manager from Database layer. IORM only actively manages I/O requests when needed and when Storage Server is not fully utilized, it provide data immediately. But when a disk is under heavy utilzation, Storage Server software redirects the I/O requests to the appropriate IORM queue and schedules I/O from there according to the policies defined in your IORM plans. Generally IORM policies open the way to prioritize databases on intelligent storage layer, which enable workload optimization.

Oracle Database Backup To Cloud (to Amazon S3)

Oracle Database Backup To Cloud:
Amazon Simple Storage Service (S3)

Amazon Web Services (AWS) is the first Cloud vendor that Oracle has partnered with to enable database backup in the Cloud.

The process is:

1. Create an AWS S3 account and setup the necessary credentials.
2. Install an AWS specific Oracle Secure Backup library into your Oracle Home.
3. Run an RMAN backup using the SBT_TAPE device type.

Creating an AWS S3 Account

Go to -> aws.amazon.com

Under IAM (Identify & Access Management) , select user -> add user

 

Installing the Oracle Secure Backup Cloud Module Jar file

The Oracle Secure Backup (OSB) Cloud Module enables an Oracle Database to send its backups to Amazon S3. It is compatible with Oracle Database versions 9i Release 2 and above, and it requires a network connection to the Internet




From OTN download an installer Java JAR file and copy and extract the zip to your database server. When run, the installer will determine the proper database version and OS platform, as well as download the appropriate library file to your Oracle home or other specified directory.

$ java -jar osbws_install.jar \
>    -AWSID AKI***************QA \
>    -AWSKey no/MD*******************************upxK \
>    -otnUser vincent.ng@xxxx.com \
>    -walletDir $ORACLE_HOME/dbs/osbws_wallet \
>    -libDir $ORACLE_HOME/lib

RMAN> run {
allocate channel aws_s3 type sbt
parms='SBT_LIBRARY=libosbws.so,SBT_PARMS=(OSB_WS_PFILE=/u01/app/oracle/product/12.1.0/dbhome_2/dbs/osbwsCDB121.ora)';
backup tablespace users;
}