1Z0-883 | Realistic Oracle 1Z0-883 practice test
Q41. You need to replicate a table from a master to a slave. The master and slave copies of the
table will have different number of columns.
Which two conditions must be true?
A. Each extra column in the copy with more columns must not have a default value.
B. Columns that are common to both versions of the table must be defined in the same order on the master and the slave.
C. The slave database cannot have more columns than the master. Only the master database can have more columns.
D. Columns that are common to both versions of the table must come first in the table definition, before any additional columns are additional columns are defined on either server.
E. The master database cannot have more columns than the slave. Only the slave deatbase can have more columns.
Answer: A,E
Q42. You are attempting to secure a MySQL server by using SSL encryption.
On starting MySQL, you get this error:
130123 10:38:02 [ERROR] mysqld: unknown option ‘—ssl’
What is the cause of the error?
A. The --- ssl level was not specified.
B. The server was not started with the – enable--ssl-plugin option.
C. -- ssl is not a valid server option.
D. The mysqld binary was not compiled with SSL support.
E. The server’s SSL certificate was invalid.
Answer: B
Q43. A user executes the statement;
PURGE BINARY LOGS TO ‘mysql-bin.010’;
What is the result?
A. It deletes all binary log files, except ‘mysql-in.010’.
B. It deletes all binary log files up to and including ‘mysql-bin.010’.
C. It deletes all binary log files before ‘mysql-bin.010’.
D. It deletes all binary log files after ‘mysql-bin.010’.
Answer: B
Reference: http://dev.mysql.com/doc/refman/5.5/en/purge-binary-logs.html
Q44. Which two are correct steps in taking a binary backup of MyISAM tables?
A. Always stop the server prior to the backup.
B. Stop the server or lock the tables prior to the backup.
C. Stop the server or lock the databases prior to the backup.
D. Make a copy of the .frm, .myd, and the .myi files.
E. Make a copy of the binary log and tablespace files.
Answer: B,D
Reference: http://dev.mysql.com/doc/refman/5.6/en/backup-methods.html
Q45. Consider the following table:
CREATE TABLE ‘game’ (
‘id’ int (10) unsigned NOT NULL AUTO_INCREMENT,
‘keyword’ varchar (45) DEFAULT NULL,
‘date’ datetime NOT NULL,
PRIMARY KEY (‘id’ , ‘date’),
UNIQUE KEY ‘keyword_idx’ (‘keyword’ , ‘date’)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY RANGE (TO_DAYS (date) ) (
PARTITION g201301 VALUES LESS THAN (TO_DAYS (‘2013-01-01 00:00:00’) ),
PARTITION g201302 VALUES LESS THAN (TO_DAYS (‘2013-02-01 00:00:00’) ),
PARTITION g201303 VALUES LESS THAN (TO_DAYS (‘2013-03-01 00:00:00’) ),
PARTITION g201304 VALUES LESS THAN (TO_DAYS (‘2013-04-01 00:00:00’) ),
PARTITION gMORES VALUES LESS THAN (MAXVALUE) );
Which method should used to add a new g201305 partition to the table?
A. ALTER TABLE games
REORGANIZE PARTITION (gMORES)
INTO
g01305 VALUES LESS THAN (TO_DAYS (‘2013-05-01 00:00:00’) ),
gMORES VALUES LESS THAN (MAXVALUE) );
B. ALTER TABLE games
ADD PARTITION g201350 VALUES LESS THAN (TO_DAYS (‘2013-05-01 00:00:00’) );
C. ALTER TABLE games
COALESCE PARTITION (gMORES)
INTO
g01305 VALUES LESS THAN (TO_DAYS (‘2013-05-01 00:00:00’) ),
gMORES VALUES LESS THAN (MAXVALUE) );
D. ALTER TABLE games
SPLIT PARTITION (gMORES)
INTO
g201305 VALUES LESS THAN (TO_DAYS (‘2013-05-01 00:00:00’) ),
gMORES VALUES LESS THAN (MAXVALUE) );
E. ALTHER TABLE games
DROP PATITION gMORES,
ADD PARTITION
g201305 VALUES LESS THAN (TO_DAYS (‘2013-05-01 00:00:00’) ),
gMORES VALUES LESS THAN (MAXVALUE) );
Answer: B
Q46. 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.
Answer: B
Q47. Following a server crash, the automatic recovery of InnoDB fails.
How would you begin to manually repair the InnoDB tables?
A. Start the server with the – innodb_force_recovery option set to a non-zero value.
B. Start the server as usual, and then execute the REPAIR TABLE command.
C. Start the server as usual, and then execute the CHECK TABLE command.
D. Start the server with the – innodb_recover_options option set to FORCE.
Answer: A
Q48. Consider the MySQL Enterprise Audit plugin.
On attempting to start the MySQL service after a crash, notice the following error:
[ERROR] Plugin ‘audit_log’ init function returned error.
In the audit log file, you notice the final entry:
…
<AUDIT_RECORD
TIMESTAMP=”2013-07-09T02:12:35”
NAME=”Connect”
CONNECTION_ID=”98”
STATUS=”0”
USER=”Kate”
PRIV_USER=”kate”
OS_LOGIN=””
HOST=”localhost”
DB=””/>
What action should you take to fix the error and allow the service to start?
A. Re-install the audit plugin.
B. Execute the command FLUSH LOGS.
C. Execute the command SET GLOBAL audit_log_fiush= ON.
D. Move or rename the existing audit.log file.
Answer: B
Q49. You are using CTIDS in replication. You need to skip a transaction with the CTID of aaa-bbb-ccc-ddd-eee : 3 on a slave.
Which command would you execute from a Mysql prompt?
A. STOP SLAVE;
SETGTID_NEXT=”aaa-bbb-ccc-ddd-eee: 3”;
BEGIN;
COMMIT;
SET GTID_NEXT=”AUTOMATIC”;
START SLAVE
B. STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
START SLAVE;
C. STOP SLAVE;
BEGIN;
SET GTID_IGNORE=”aaa-bbb-ccc-ddd-eee: 3”;
COMMIT;
START SLAVE;
D. STOP SLAVE;
RESET SLAVE;
BEGIN;
SKIP NEXT GTID;
COMMIT;
START SLAVE;
Answer: B
Reference: http://blog.secaserver.com/2011/11/resync-mysql-masterslave-replication/
Q50. You are investigating the performance of the server and see the following information:
Events_waits_summary_global_by_event_name in the performance schema
shows that the wait/synch/mutex/sql/LOCK_table_cache event is dominating other
wait events.
The table_open_cache_overflows status variable is 0.
Which action should be taken to remove the performance bottleneck described here?
A. Decrease the value of table_definition_cache.
B. Increase the value of table_definition_cache.
C. Decrease the value of table_open_cache.
D. Increase the value of table_open_cache.
E. Decrease the value of table_open_cache_instances.
F. Increase the value of table_open_cache_instances.
Answer: A,D