Typical Steps in Machine Learning

Frame the problem

The first thing you have to do before you solve a problem is to define exactly what it is

Data Collection: The very first and the most important step is to collect relevant data corresponding to our problem statement

Data Pre-Processing: The data gathered from the previous step most probably is not fit to be used by our machine learning algorithm yet, as this data might be incomplete, inconsistent and is likely to contain many errors and missing values

• Missing values, perhaps customers without an initial contact date
• Corrupted values, such as invalid entries

Creating Transformers

Before we can use the dataset to estimate a model, we need to do some transformation.

Spark machine learning algorithms work with two columns that must be named features and label, by default. The features column must be a vectorrepresentation of the features we intend to use to estimate a model while the label column represents the column with the different outcomes

Creating an Estimator

We shall now create our estimator

Creating a Pipeline

Now, create a Pipeline to pull the different transformations together:

Model Evaluation: After the model is trained, it is then evaluated, using some evaluation metric, on the test dataset

Performance Improvement: The performance of the model can further be improved on both the training and testing datasets

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