Oracle Oracle Database Application Development 1Z0-883 Questions & Answers

• Question 41:

  You have forgotten the root user account password. You decide to reset the password and execute the following:

  Shell> /etc/init.d/mysql stop Shell> /etc/init.d/mysql start skip-grant tables

  Which additional argument makes this operation safer?

  A. --skip-networking, to prohibit access from remote locations

  B. --reset-grant-tables, to start the server with only the mysql database accessible

  C. --read-only,to set all data to read-only except for super users

  D. --old-passwords, to start Mysql to use the old password format while running without the grant tables

  Correct Answer: B

• Question 42:

  Which two requirements would lead towards a high availability solution?

  A. When uptime is critical

  B. When data must be refactored

  C. When application concurrency is static

  D. When data loss is unacceptable

  E. When application is a single point of failure

  Correct Answer: AD

• Question 43:

  Which statement is true about using Microsoft Windows Cluster as a platform for Mysql?

  A. It is provided by means of IP- level disk replication.

  B. It is shared-nothing architecture.

  C. It implements High Availability by using the .NET Connector's load balancing capabilities.

  D. It relies on the shared disk architecture being visible to both servers.

  Correct Answer: D

• Question 44:

  You have enabled the Slow Query Log for a short period.

  When you process the Slow Query Log, you receive the following snip of output:

  Count: 100 Time=0 .22a (22s) Lock=0.00s (0s) Rows=0.0 (0), root[root] @localhost CREATE TABLE

  `t1' (id serial,id0 varchar(N) unique key,intcaoll INT (N) ,intco12 INT(N) ,intco13 INT(N) ,intco14 INT

  (N) ,intco15 INT(N) ,charcol1 VARVHAR(N) ,charcol2 VARCHAR(N) charcol3 VARCHAR (N) ,charcol4 VARVHAR(N) ,charcol5 VARCHAR(N) charcol6 VARCHAR (N) ,charcol7 VARVHAR(N) ,charcol8 VARCHAR(N) charcol9 VARCHAR (N) .charcol 10 VACHAR (N) )

  Count: 64000 Time-0.02s (1213s) Lock=0.00s (6s) Rows=1.0 (64000), root [root]@ localhost SELECT intocl1, intco12, intco13, intco14, intco15, intco16,intco17, intco18 ,intcol9, intcol10, charcol1, charcol2, charcol3, charcol4, charcol5, charcol6 ,charcol7, charcol8, charcol9, charcol10 FROM t1 WHERE id = `s'

  Count: 1 Time=0.02s (0s) Lock=0.00s (0s) Rows=1.0 (1) agent [agent] @localhost SELECT Select_priv, Repl_client_priv, Show_db_priv, Super_priv, Process_priv FROM mysql.user WHERE CONCAT (user, `s', host) = CURRENT_USER () Count: 48000 Time=0.02s (778s) Lock=0.00 (3s) Rows=1.0 (48000), root [root]@localhost SELECT intocl1,intcol2,intcol3, intcol4, intcol5, charcol1, charcol2, charcol3 ,charcol4, charcol5, charcol6, charcol7, charcol8, charcol9, charcol10 FROM t1 WHERE id = `s'

  You want to tune the query such that it provides the greatest overall time savings.

  Which query will accomplish this?

  A. CHEATE TABLE `t1' (id serial, id0 varchar (N) unique key, intcol1 INT (N) ,intcol2 INT (N), intcol3 INT

  (N) ,intcol4 INT(N), intcol5 INT(N), charool1 VARCHAR (N)

  ,charcol2 VARCHAR (N), charcol3 VARCHAR(N), charcol4 VARCHAR(N), charcol5 VARCHAR (N)

  ,charcol6 VARCHAR (N), charcol7 VARCHAR(N), charcol8 VARCHAR(N), charcol9 VARCHAR (N)

  ,charcol10 VARCHAR (N);

  B. SELECT intcol1, intcol2, intcol3, intcol4, intcol5, intcol6, intcol7, intcol8, intcol9, Intcol10, intcol11, intcol12, intcol13, intcol14, intcol15, intcol16, intcol17, intcol18, intcol19, charcol10 FROM t1 WHERE id = `s';

  C. SELECT Select_priv, Repl_client_priv, Show_db_priv, Super_priv, Process_priv FROM mysql.user WHERE CONCAT (user,'s', host) = CURRENT_USER();

  D. SELECT intcol1, intcol2, intcol3, intcol4, intcol5, charcol1, charcol2, charcol3, charcol4, charcol5, charcol6, charcol7, charcol8, charcol9, charcol10 FROM t1 WHERE id = `s';

  Correct Answer: A

• Question 45:

  Review the definition of the phone_list view.

  CHEATE OR REPLACE ALGORITHM=MERGE DEFINER= `root'@localhost' SQL SECURITY DEFINER VIEW `phone_list' AS SELECT

  e . id as id

  `e . first_name AS `first_name'

  `e . last_name AS `last_name'

  `coalesce ( ph1.phone_no, `--') AS `office_no'

  `coalesce (ph2 .phone_no, '--`) AS `cell_no'

  FROM employees e

  LEFT JOIN employee_phone ph1

  ON ph1.emp_id = e.id AND ph1.type = `office'

  LEFT JOIN employee_phone ph2

  ON ph2 .emp_id = e.id AND ph2 .type = `mobile'

  The tables employees and employee_phone are InnoDB tables; all columns are used in this view.

  The contents of the phone_list view are as follows:

  Mysql> select * from phone_list;

  

  1 row in set (0.00 sec) Which method can you use to change the cell_no value to `555-8888' for John Doe?

  A. DELETE FROM phone_list WHERE first_name= `John' and last_name= `Doe'; INSERT INTO phone_list (first_name, last_name, office_no, cell_no) VALUES (`John' , `Doe' , `x1234' , `555-8888);

  B. INSERT INTO employee_phone (emp_id, phone_no, type) VALUES (1, `555- 8888','mobile');

  C. UPDATE phone_list SET cell_name `555-8888' WHERE first_name= `John' and last_name= `Doe';

  D. UPDATE employee_phone SET phone_no= `555-8888' where emp_id=1;

  Correct Answer: B

• Question 46:

  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

  Correct Answer: A

• Question 47:

  Which MySQL utility program should you to process and sort the slow Query log based on query time or average query time?

  A. Mysqlslow

  B. Mysqldumpslow

  C. Mysqlshow

  D. Mysqldump

  E. Mysqlaccess

  Correct Answer: B

• Question 48:

  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.

  Correct Answer: B

• Question 49:

  You have been notified that the `apps' . `reports' table has been accidentally truncated.

  You have single file mysqldump backup available taken prior to the truncate. The backup contains all the tables from the instance, and the `apps' . `reports' table must be restored without affecting the other remaining databases and tables.

  Which restore option is suitable in this scenario?

  A. Restore the backup to another databases instance and obtain a copy of the reports table individually.

  B. Extract the `apps' . `reports' table from the backup using the SOURCE command.

  C. Execute LOAD DATA INFILE `backup.sql' SCHEMA='apps' TABLE= `reports'

  D. Execute mysqldump on the backup,sql file and apply filter arguments to obtain only the `apps' . `reportys' table.

  Correct Answer: B

• Question 50:

  An existing master-slave setup is currently using a delayed replication of one hour. The master has crashed and the slave must be "rolled forward" to provide all the latest data.

  The SHOW SLAVE STATUS indicates the following values:

  RELAY_LOG_FILE = hostname-relay-bin.00004 RELAY_LOG_POS = 1383 Which command set would make the slave current?

  A. STOP SLAVE; CHANGE MASTER TO MASTER_DELAY=0; START SLAVE;

  B. STOP SLAVE; CHANGE MASTER TO MASTER_DELAY =0, RELAY_LOG_FILE = `hostname-relaybin.00004' , RELAY_LOG_POS = 1383;

  C. STOP SLAVE; CHANGE MASTER TO RELAY_LOG_FILE = `hostname-relay-bin.00004', RELAY_LOG_POS = 1383;

  D. STOP SLAVE; SET GLOBAL master_delay =0; START SLAVE;

  Correct Answer: A