Oracle Oracle Certifications 1Z0-083 Questions & Answers

• Question 231:

  Which three are true about the SQL Tuning Advisor? (Choose three.)

  A. It checks each query being analyzed for stale statistics.

  B. It checks each query being analyzed for missing statistics.

  C. It only recommends syntactic changes to SQL statements.

  D. It can recommend semantic changes to SQL statements.

  E. It considers all SQL statements being analyzed by the advisor task as a group.

  F. It builds SQL profiles for each poorly performing SQL statement to prevent regressions.

  Correct Answer: ABD

  Not F

  "for improving performance of high-load SQL statements" NOT "for each poorly performing SQL statement"

  SQL Tuning Advisor is a mechanism for resolving problems related to suboptimally performing SQL statements.

  Use SQL Tuning Advisor to obtain recommendations for improving performance of high-load SQL statements, and prevent regressions by only executing optimal plans.

  Reference: https://docs.oracle.com/en/database/oracle/oracle-database/18/tgsql/sql-tuning-advisor.html#GUID-73AB38C1-A7F6-401E-9010-B4476E173673

• Question 232:

  A database is configured in ARCHIVELOG mode.

  Full RMAN backups are taken and no backup to trace has been taken of the control file.

  A media failure has occurred.

  In which two scenarios is complete recovery possible? (Choose two.)

  A. when any archived log from, before, or after the most recent backup is corrupt.

  B. after losing all copies of the control file

  C. after losing an archived log from after the most recent backup

  D. after losing an archived log from before the most recent backup

  E. after losing the SYSTEM tablespace

  Correct Answer: DE

  If you lose any archive from after the backup, you can't do a complete recovery, so A and C are discarted, also you can't mount the database if you don't have a valid control file. (not B)

• Question 233:

  Which two are true about Recovery Manager (RMAN) diagnostic message output? (Choose two.)

  A. Media Management messages for SBT devices are always written to sbtio.log.

  B. RMAN error stacks should be read from the bottom up as that is the order in which errors are generated.

  C. RMAN error stacks should be read from the top down as that is the order in which errors are generated.

  D. The RMAN LOG command line clause causes output issued during RMAN command compilation to be written to a log file and to standard output.

  E. The RMAN LOG command line clause causes output issued during RMAN command compilation to be written to a log file only.

  F. Media Management messages for SBT devices are written to an Oracle trace file.

  Correct Answer: BE

  [oracle@rac1 admin]$ rman target / log '/tmp/log.txt'

  RMAN> list backup summary;

  RMAN> exit

  [oracle@rac1 admin]$ cat /tmp/log.txt

  Recovery Manager: Release 19.0.0.0.0 - Production on Mon Jun 22 15:48:39 2020

  Version 19.6.0.0.0

  Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

  connected to target database: TST193 (DBID=1785824688)

  RMAN>

  using target database control file instead of recovery catalog

  List of Backups

  ===============

  Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag

  1 B F A DISK 15-JUN-20 1 1 NO TAG20200615T165352 2 B F A DISK 15-JUN-20 1 1 NO TAG20200615T174406 3 B F A DISK 22-JUN-20 1 1 NO TAG20200622T095655 4 B F A DISK 22-JUN-20 1 1 NO TAG20200622T095942 5 B F A DISK 22-JUN-20 1 1 NO TAG20200622T154703

  RMAN>

  Recovery Manager complete.

• Question 234:

  Examine the command for creating pluggable database PDB2 in container database CDB2.

  

  Select three options, any one of which is required for it to execute successfully? (Choose three.)

  A. Add the FILE_NAME_CONVERT clause to the statement and set the PDB_FILE_NAME_CONVERT parameter.

  B. Add only the CREATE_FILE_DEST clause to the statement.

  C. Set only the PDB_FILE_NAME_CONVERT parameter.

  D. Set the PDB_FILE_NAME_CONVERT parameter and enable OMF.

  E. Enable only OMF.

  F. Add the FILE_NAME_CONVERT clause to the statement and enable Oracle Managed Files (OMF)

  Correct Answer: BCE

  You can specify FILE_NAME_CONVERT = NONE, which is the same as omitting this clause. If you omit this clause, then the database first attempts to use Oracle Managed Files to generate file names. If you are not using Oracle Managed Files, then the database uses the PDB_FILE_NAME_CONVERT initialization parameter to generate file names. If this parameter is not set, then an error occurs.

  Reference: https://docs.oracle.com/database/121/SQLRF/statements_6010.htm#SQLRF55686

• Question 235:

  Which two are true about Oracle Flashback features? (Choose two.)

  A. FLASHBACK QUERY can retrieve REDO records from ONLINE and ARCHIVED REDO LOG files.

  B. FLASHBACK VERSION QUERY can retrieve REDO records from ONLINE and ARCHIVED REDO LOG files.

  C. FLASHBACK TABLE can undrop a column.

  D. FLASHBACK DROP can undrop an index when undropping a table.

  E. After a database is restored from flashback logs using the FLASHBACK DATABASE command, it is sometimes rolled forward using redo logs.

  Correct Answer: DE

  A, B are Wrong. Flashback Query and Version Query need Undo not Redo

  C is Wrong.

  D is Correct. Assuming that an index is not dropped before dropping a table.

  E is Correct

• Question 236:

  Which three are true about an application seed pluggable database (PDB)? (Choose three.)

  A. It is automatically synchronized with its application root PDB when an application is upgraded.

  B. It cannot be added to an application container after the application container has already been created.

  C. A new application PDB created by cloning an application seed PDB can have an old version of the application installed after cloning completes.

  D. It is automatically synchronized with its application root PDB when an application is installed.

  E. It cannot be dropped from its application container.

  F. A new application PDB created by cloning an application seed PDB can have an up-to-date version of the application installed after cloning completes.

  G. It is not required in an application container.

  Correct Answer: CFG

  https://docs.oracle.com/en/database/oracle/oracle-database/19/multi/creating-removing-application-containers-seeds-with-sql-plus.html#GUID-065649AC-18ED-4858-ACC5-36F011362A83

• Question 237:

  Examine this configuration:

  1.

  CDB1 is a container database.

  2.

  PDB1 and PDB2 are pluggable databases in CDB1.

  3.

  PDB1 and PDB2 are OPEN in READ WRITE mode. You execute these commands successfully:

  

  Which two are true? (Choose two.)

  A. Uncommitted transactions in PDB1 have been rolled back.

  B. PDB1 is closed.

  C. Uncommitted transactions in CDB1 and PDB1 have been rolled back.

  D. CDB1 is shut down.

  E. CDB1 is in MOUNT state

  Correct Answer: AB

• Question 238:

  Which three are true about Automatic Workload Repository (AWR), Automatic Database Diagnostic Monitor (ADDM), and the Manageability Monitor (MMON) background process? (Choose three.)

  A. ADDM can recommend shrinking the buffer cache.

  B. ADDM can recommend extending the buffer cache.

  C. By default, MMON creates an AWR snapshot every 30 minutes.

  D. ADDM performs its analysis only when a DBA requests it.

  E. By default, AWR snapshots are automatically purged after eight days.

  F. AWR snapshots must be deleted when no longer required by ADDM.

  Correct Answer: ABE

  Reference: https://books.google.com.pk/books?id=DeQkU5E75DUCandpg=PA869andlpg=PA869anddq=By+default,+AWR+snapshots+are+automatically+purged+after+eight +daysandsource=blandots=DfoGmJfCMyandsig=ACfU3U0Pf2RgIs72LUV4HUzs5UIXI8sXEQandhl=enandsa=Xandved=2ahUKEwjb8bKhu9noAhUwTxUIHRpYAuMQ6AEwA3oECAsQLA#v=onepageandq=By%20default%2C%20AWR%20snapshots%20are %20automatically%20purged%20after%20eight%20daysandf=false

• Question 239:

  You are managing this configuration:

  1.

  CDB1 is a container database.

  2.

  PDB1 and PDB2 are two pluggable databases in CDB1.

  3.

  USER1.EMP is a table in PDB1 and USER2.DEPT is a table in PDB2. CDB1 user SYS executes these commands after connecting successfully to PDB2:

  

  Which two are true? (Choose two.)

  A. The inserts on USER1.EMP remain uncommitted when the session connected to PDB2.

  B. The inserts on USER1.EMP were committed when the session inserted a row into USER2.DEPT.

  C. The insert on USER2.DEPT fails because of the active transaction in the parent container.

  D. The insert on USER2.DEPT is a recursive autonomous transaction by the child session and is committed.

  E. The inserts on USER1.EMP were rolled back when the session connected to PDB2.

  F. The insert on USER2.DEPT is uncommitted.

  G. The inserts on USER1.EMP were committed when the session connected to PDB2.

  Correct Answer: AC

  other session to check any transaction

  Version 19.5.0.0.0

  SQL>

  SQL> alter session set container=ELC20MIG;

  Session altered.

  SQL> select * from user1.emp;

  no rows selected

• Question 240:

  Examine this configuration:

  1.

  CDB1 is an Oracle Database 12c Release 2 database containing pluggable databases PDB$SEED, PDB1, and PDB2.

  2.

  PDB$SEED is open READ ONLY

  3.

  PDB1 is open READ WRITE

  4.

  PDB2 is MOUNTED.

  5.

  ORACLE_HOME is /u01/app/oracle/product/18.1.0/dbhome_1.

  You execute these commands before upgrading the database to the current release:

  

  For which databases will fixup scripts be created?

  A. CDB1, PDB$SEED, PDB1, and PDB2

  B. PDB$SEED, PDB1, and PDB2 only

  C. CDB1 and PDB$SEED only

  D. CDB1, PDB1, and PDB2 only

  E. CDB1, PDB$SEED, and PDB1 only

  Correct Answer: E

  For multitenant architecture (CDB and PDB) upgrades, open up all the PDBs that you want the tool to analyze before you run the tool.