Oracle 1Z0-883 Free Practice Questions

Q1. A user has deleted the wrong row in a table and you are preparing a point-in-time recovery skipping the DELETE event. 

The server is configured with: 

You have identified that the DELETE statement to skip has the Global Transaction Identifier (GTID) ‘dbbe7da-fe25-11e2-b6c7-0800274aa49e:5 and you replay the binary log with: 

Mysqlbinlog – exclude-gtides=’ dbbe07da-fe25-11e2-b6c7-0800274aa49e:5’ binlog.00000.2 | mysql 

However all events were skipped instead of just the one deleting the wrong row. 

What is the reason for this? 

A. Mysqlbinlog ignores arguments to – exclude-gtids-it means ignore all events with GTIDs. 

B. The server keeps track of which GTIDs have already been executed and skips those. 

C. Enforce_gtid_consistency is set to ON. 

D. Gtid_mode must be set to AUTO during point in time recoveries. 

View Answer

Q2. The following commands are available in the Linux binary distributions of Mysql: 

Mysqld Mysqld_safe Mysql.server 

What is the correct description of each of these commands? 

A. Mysqld is the server. 

Mysqld_safe is a shell script that invokes mysqld. 

Mysql.server is a wrapper for mysql_safe. 

B. Mysqld is a shell script that starts mysql.server. 

Mysqld_safe causes the server to start up in data recovery mode. 

Mysql.server is the server. 

C. Mysqld is the server. 

Mysqld_safe causes the server to start up in data recovery mode. 

Mysql.server is a wrapper for mysqld_safe. 

D. Mysql, mysqld.safe, and mysql.server reside in different locations but are all symlinked 

to the same script. 

View Answer

Q3. Which three data components are needed for point-in-time recovery? 

A. The error log 

B. The backup log 

C. The general query log 

D. Binary logs 

E. The data backup 

F. Configuration files 

View Answer

Q4. Which statement is true about FLUSH LOGS command? 

A. It requires the RELOAD, FILE, and DROP privileges. 

B. It closes and reopens all log files. 

C. It closes and sends binary log files to slave servers. 

D. It flushes dirty pages in the buffer pool to the REDO logs. 

View Answer

Q5. The InnoDB engine has a feature known as clustered indexes. 

Which three statements are true about clustered indexes as used in InnoDB? 

A. A primary key must exist for creation of a clustered index. 

B. A primary key is used as a clustered index. 

C. A clustered index is a grouping of indexes from different tables into a global index for faster searching. 

D. If no indexes exist, a hidden clustered index is generated based on row IDs. 

E. A clustered index provides direct access to a page containing row data. 

F. The first unique index is always used as a clustered index and not a primary key. 

G. A clustered index allows fulltext searching within InnoDB, 

View Answer

Q6. The ‘allplicationdb’ is using innoDB and consuming a large amount of file system space. You have a /backup partition available on NFS where backups are stored. 

You investigate and gather the following information: 

[mysqld] Datadir=/var/lib/mysql/ Innodb_file_per_table=0 

Three tables are stored in the innoDB shared tablespace and the details are as follows: 

The table data_current has 1,000,000 rows. 

The table data_reports has 1,500,000 rows. 

The table data_archive has 4,500,000 rows. Shell> is -1 /var/lib/mysql/ -rw-rw---- 1 mysql mysql 744G Aug 26 14:34 ibdata1 -rw-rw---- 1 mysql mysql 480M Aug 26 14:34 ib_logfile0 -rw-rw---- 1 mysql mysql 480M Aug 26 14:34 ib_logfile1 … 

You attempt to free space from ibdata1 by taking a mysqldump of the data_archive table and storting it on your backup partition. 

Shell> mysqldump – u root – p applicationdb data_archive > /backup/data_archive.sql Mysql> DROP TABLE data_archive; 

Which set of actions will allow you to free disk space back to the file system? 

A. Execute OPTIMIZE TABLE so that the InnoDB engine frees unused pages on disk back 

to the file system: 

Mysql> OPTIMIZE TABLE data_current, data_reports; 

B. Set the server to use its own tablespace, and then alter the table so that data is moved 

from the shared tablespace to its own: 

Mysql> SET GLOBAL innodb_file_per_table=1; 

Mysql> ALTER TABLE data_current ENGINE=InnoDB; 

Mysql> ALTER TABLE data_repors ENGINE=InnoDB; 

C. Take a backup, stop the server, remove the data files, and restore the backup: 

Shell> mysqldump – u root –p applicationdb / > /backup/applicationdb.sql 

Shell> /etc/init.d/mysql stop 

Shell> cd /var/lib/mysql/ 

Shell> rm ibdata1 ib_logfile0 ib_logfile1 

Shell> /etc/init.d/mysql start 

Shell> mysql – u root – p applicationdb < /backup/applicationdb.sql 

D. Enable compression on the table, causing InnoDB to release unused pages on disk to 

the file system: 

Mysql> SET GLOBLE innodb_file_per_table=1; 

Mysql> SET GLOBLE innodb_file_format=Barramcuda; 

Mysql> ALTER TABLE data_current ROW_FORMAT=COMPRESSED 

KEY_BLOCK_SIZE=8; 

Mysql> ALTER TABLE data_history ROW_FORMAT=COMPRESSED 

KEY_BLOCK_SIZE=8; 

View Answer

Q7. Which High Availability solution can provide a consistent, time-delayed (for example, one hour) snapshot of the live production database? 

A. MySQL Replication 

B. Distributed Replication Block Device 

C. Windows Server Failover Clustering 

D. MySQL Cluster 

View Answer

Q8. Which two are true regarding MySQL binary and text backups? 

A. Binary backups are usually faster than text backups. 

B. Binary backups are usually slower than text backups. 

C. Text backups are human-readable while binary backups are not. 

D. Binary backups are not portable across different operating systems. 

View Answer

Q9. A Mysql instance is running on a dedicated server. Developers access the server from the same network subnet. Users access the database through an application that is running on a separate server in a DMZ. 

Which two will optimize the security of this setup? 

A. Disabling connections from named pipes or socket files (depending on the operating system of the server) 

B. Running the server with – skip-networking specified 

C. Limiting logins to originate from the application server or the server’s subnet 

D. Starting the server with – bind- address=0.0.0.0 specified 

E. Installing Mysql on the application server, and running the database and application on the same server 

F. Enabling and using SSL for connections to the Mysql database 

View Answer

Q10. In a design situation, there are multiple character sets that can properly encode your data. Which three should influence your choice of character set? 

A. Disk usage when storing data 

B. Syntax when writing queries involving JOINS 

C. Comparing the encoded data with similar columns on other tables 

D. Memory usage when working with the data 

E. Character set mapping index hash size 

View Answer

Q11. Which two options describe how MySQL Server allocates memory? 

A. Each thread allocates memory from a global pool. 

B. Global memory resources are allocated at server startup. 

C. Thread memory is pre-allocated up to thread_cache_size for performance. 

D. Each connection may have its own per-thread memory allocations. 

View Answer

Q12. In a test database, you issue the SELECT … INTO OUTFILE statement to create a file with your t1 table data. 

You then TRUNCATE this table to empty it. 

Mysql> SELECT * INTO OUTFILE ‘/tmp/t1.sql’ from t1; 

mysql> TRUNCATE t1; 

Which two methods will restore data to the t1 table? 

A. Mysql> LOAD DATA INFILE ‘/tmp/t1.sql’ INTO TABLE t1; 

B. $ mysqladmin – u root – p – h localhost test – restore /tmp/t1.sql 

C. $ mysql – u root – p – h localhost test < /tmp/t1.sql 

D. $ mysqlinport – u root – p – h localhost test /tmp/t1.sql 

E. Mysql> INSERT INTO t1 VALUES FROM ‘/tmp/t1.sql’; 

View Answer

Q13. MySQL is installed on a Linux server and has the following configuration: 

[mysqld] 

User=mysql 

Datadir=/data/mysql 

As the ‘root’ user, change the datadir location by executing: 

Shell> cp –R /var/lib/mysql/data/mysql/ 

Shell> chown –R mysql /data/mysql/ 

What is the purpose of changing ownership of datadir to the ‘mysql’ user? 

A. MySQL cannot be run as the root user. 

B. MySQL requires correct file ownership while remaining secure. 

C. MySQL needs to be run as the root user, but file cannot be owned by it. 

D. The mysqld process requires all permissions within datadir to be the same. 

View Answer

Q14. You want to start monitoring statistics on the distribution of storage engines that are being used and the average sizes of tables in the various databases. 

Some details are as follows: . The Mysql instance has 400 databases. . Each database on an average consists of 25-50 tables. 

You use the query: 

SELECT TABLE_SCHEMA, 

‘ENGINE’, 

COUNT (*), 

SUM (data_length) total_size 

FROM INFORMATION_SCHEMA.TABLES 

WHERE TABLE_TYPE = ‘BASE TABLE’ 

GROUP BY TABLE_SCHEMA, ‘ENGINE’ 

; 

Why is this query slow to execute? 

A. Counting and summarizing all table pages in the InnoDB shared tablespace is time consuming. 

B. Collecting information requires various disk-level operations and is time consuming. 

C. Aggregating details from various storage engine caches for the final output is time consuming. 

D. Collecting information requires large numbers of locks on various INFORMATION_SCHEMA tables. 

View Answer

Q15. Consider the three binary log files bin.00010, bin.00011, and bin.00012 from which you want to restore data. 

Which method would use mysqlbinlog for greater consistency? 

A. shell> mysqlbinlog bin.00010 | mysql shell> mysqlbinlog bin.00011 | mysql shell> mysqlbinlog bin.00012 | mysql 

B. shell> mysqlbinlog bin.00010 bin.00011 bin.00012 | mysql 

C. shell> mysqlbinlog – restore bin.00010 bin.00011 bin.00012 

D. shell> mysqlbinlog – include-gtide=ALL bin.00010 bin.00011 bin.00012 | mysql 

View Answer