Oracle Oracle Certifications 1Z0-888 Questions & Answers

• Question 81:

  There are multiple instances of MySQL Server running on a single OS that is backed up using the

  mysqlbackup command.

  The /etc/my/cnf contains default values, for example, datadir=/var/lib/mysql/, with extra instances having

  their own separate my.cnf file (for example /etc/mysql/instanceN.cnf) overriding the defaults.

  A restore of the second instance is attempted from the mysqlbackup archive using this command:

  mysqlbackup --backup-dir=/opt/backup/mysql/instance2 copy-back

  Upon starting the second MySQL instance, you notice that the data does not match the expected backup.

  Which command-line option is required to successfully update the second instance?

  A. --restore=2

  B. --copy-back-from-log

  C. --backup-instance=/var/lib/mysql/instance2

  D. --instance=/var/lib/mysql/instance2

  E. --defaults-file=/etc/mysql/instance2.cnf

  Correct Answer: E

• Question 82:

  You have created a new user with this statement:

  CREATE USER `erika'@'localhost' IDENTIFIED BY `first#1Pass' PASSWORD EXPIRE;

  What is the outcome?

  A. When `erika'@'localhost' tries to log in with the MySQL command-line client, the user will have to change the password before seeing the mysql> prompt.

  B. When `erika'@'localhost' tries to log in with the MySQL command-line client, the user will not be permitted to log in because the password is expired.

  C. When `erika'@'localhost' tries to log in with the MySQL command-line client, the user will be permitted to log in but will not be able to issue ant statements until the user changes the password.

  D. You receive a syntax error that indicates that you cannot set a password and expire it at the same time.

  Correct Answer: A

• Question 83:

  Which two statements are true regarding the creating of new MySQL physical and logical backups? (Choose two.)

  A. Physical backups can be used to recover from data corruption.

  B. Logical backups are human-readable whereas physical backups are not.

  C. Logical backups are always larger than physical backups.

  D. Physical backups are usually slower than text backups.

  E. Physical backups are usually faster than text backups.

  Correct Answer: AE

• Question 84:

  You are using the Performance Schema to investigate replication on a slave which has a single master. The option slave-parallel-type is set to DATABASE.

  

  Assume that all instruments and consumers are enabled and all threads are instrumented. Which two facts can be concluded from the given output? (Choose two.)

  A. The slave has two intermediate relay slaves connected to it.

  B. The slave is configured with slave_parallel_workers = 4

  C. At most, two schemas are being updates concurrently.

  D. THREAD_ID 21 has stopped running.

  E. The slave cannot process the relay log fast enough to use all threads.

  F. The server needs more cores to use all slave threads.

  Correct Answer: BE

• Question 85:

  Consider the join_buffer_size parameter in MySQL Server.

  Which two statements are true about the join buffer? (Choose two.)

  A. The value should be increased if the client performs several SELECT operations.

  B. The join buffer is set per connection.

  C. The join buffer is used to process sorts when complex joins are being performed.

  D. The value should be increased from the default if the query joins large rows without using an index.

  E. The join buffer is global and can be changed only by restarting the server.

  Correct Answer: CD

• Question 86:

  A single InnoDB table has been dropped by accident. You are unable to use an additional intermediate MySQL instance to restore the table. Which two backup methods can be used to restore the single table without stopping the MySQL instance? (Choose two.)

  A. a backup created with mysqldump --all-databases

  B. a backup created using FLUSH TABLES ... FOR EXPORT

  C. an up-to-date replication slave

  D. a file system-level snapshot

  E. a file system copy created while MySQL was shut down.

  Correct Answer: AD

• Question 87:

  The following grants were executed:

  GRANT CREATE ROUTING ON sales.* TO `webadmin'@'%';

  GRANT ALTER ON PROCEDURE sales.myproc TO `webadmin'@'%';

  A user successfully connects to the database as webadmin and created a stored procedure named

  get_reports. The next day, the user logs in again as webadmin and wants to delete the stored procedure

  named get_reports, and therefore, issues the following statement:

  USE sales;

  DROP PROCEDURE IF EXISTS get_reports;

  What is the result of executing the statement?

  A. The user will get an error because he or she did not use the ALTER statement to drop the stored procedure.

  B. The user will get an error because he or she did not put the database name in front of the stored procedure name.

  C. The stored procedure named get_reports will be dropped.

  D. The user will get an error because he or she does not have the permission to drop stored procedures.

  Correct Answer: C

• Question 88:

  A MySQL server was initialized with separate UNDO tablespaces. Users complain that when they roll back large transactions, the time to process the request takes too long. The DBA would like to move the MySQL InnoDB UNDO tablespace to a solid-state drive (SSD) for better performance. Is this possible and how?

  A. Yes. Shut down the mysqld process, enable the transportable_tablespace option, and move the UNDO directory to the SSD.

  B. Yes. Shut down, copy the UNDO tablespaces to the new location, and change the innodb_undo_directory value in your my.cnf.

  C. No. The UNDO tablespaces must remain on the same file system as the system tablespaces.

  D. No. The sequential write pattern of the UNDO tablespaces is not supported on modern SSD block devices.

  Correct Answer: C

• Question 89:

  The MySQL error log shows:

  InnoDB: Warning: a long semaphore wait:

  The relevant parts of the InnoDB monitor output shows: Which two options would help avoid the long wait in the future? (Choose two.)

  

  A. Increase the value of the innodb_lock_wait_timeout option.

  B. Increase the value of the innodb_read_io_threads option.

  C. Change the table to use HASH indexes instead of BTREE indexes.

  D. Set the value of innodb_adaptive_hash_index to zero.

  E. Deactivate the query cache.

  F. Increase the size of the InnoDB buffer pool.

  Correct Answer: BF

• Question 90:

  Consider the table people with the definition:

  

  The application uses a query such as:

  SELECT * FROM people WHERE YEAR(Birthday) = 1980;

  The query is not using an index.

  Which two methods can be used to allow the query to use an index? (Choose two.)

  A. Change the WHERE clause to Birthday BETWEEN 1980-01-01 AND 1980-12-31.

  B. Add a functional index for YEAR(Birthday).

  C. Execute ANALYZE TABLE to update the index statistics.

  D. Add a generated column calculating YEAR(Birthday) and index that column.

  E. Add FORCE INDEX (Birthday) to the query.

  Correct Answer: AE