Oracle Oracle Certifications 1Z0-053 Questions & Answers

• Question 211:

  While performing the backup of the flash recovery area, you notice that one of the archived redo log files is missing. You have multiple destinations for archived redo log files.

  What implications does it have on the backup of the flash recovery area?

  A. The backup fails because one of the archived redo log files is missing.

  B. The backup succeeds but it would be without the missing archived log.

  C. During backup, you are prompted for the alternative destination for the missing archived redo log file.

  D. The backup succeeds because it fails over to one of the alternative archived redo log destinations.

  Correct Answer: D

• Question 212:

  You are using Recovery Manager (RMAN) with a recovery catalog to back up your production database.

  The backups and the archived redo log files are copied to a tape drive on a daily basis. Because of media failure, you lost your production database completely along with the recovery catalog database. You want to recover the target database and make it functional. You consider performing the following steps to accomplish the task:

  1.

  Restore an autobackup of the server parameter file.

  2.

  Restore the control file

  3.

  Start the target database instance

  4.

  Mount the database

  5.

  Restore the data files

  6.

  Open the database with RESETLOGS option

  7.

  Recover the data files

  8.

  Set DBID for the target database

  Which option illustrates the correct sequence that you must use?

  A. 8, 1, 3, 2, 4, 5, 7, 6

  B. 1, 8, 3, 4, 2, 5, 7, 6

  C. 1, 3, 4, 2, 8, 5, 6, 7

  D. 1, 3, 2, 4, 6, 5, 7, 8

  Correct Answer: A

  Recovering the Database After a Disaster

  The procedure for disaster recovery is similar to the procedure for recovering the database with a backup control file in NOCATALOG mode. If you are restoring the database to a new host, then you should also review the considerations

  described in "Restoring a Database on a New Host". This scenario assumes that the Linux server on which your database was running has been damaged beyond repair. Fortunately, you backed up the database to Oracle Secure Backup

  and have the tapes available. The scenario assumes the following:

  Oracle Database is already installed on the new host. You are restoring the database to a new Linux host with the same directory structure as the old host.

  You have one tape drive containing backups of all the data files and archived redo logs through log 1124, and autobackups of the control file and server parameter file.

  You do not use a recovery catalog with the database.

  To recover the database on the new host:

  1.

  If possible, restore or re-create all relevant network files such as tnsnames.ora and listener.ora and a password file.

  2.

  Start RMAN and connect to the target database instance. At this stage, no initialization parameter file exists. If you have set ORACLE_SID and ORACLE_HOME, then you can use operating system authentication to connect as SYSDBA.

  For example, start RMAN as follows:

  % rman RMAN> CONNECT TARGET /

  3.

  Specify the DBID for the target database with the SET DBID command, as described in "Restoring the Server Parameter File".

  For example, enter the following command:

  SET DBID 676549873;

  4.

  Run the STARTUP NOMOUNT command.

  When the server parameter file is not available, RMAN attempts to start the instance with a dummy server parameter file.

  5.

  Allocate a channel to the media manager and then restore the server parameter file from autobackup.

  For example, enter the following command to restore the server parameter file from Oracle Secure Backup:

  RUN { ALLOCATE CHANNEL c1 DEVICE TYPE sbt; RESTORE SPFILE FROM AUTOBACKUP; }

  6.

  Restart the instance with the restored server parameter file. STARTUP FORCE NOMOUNT;

  7.

  Write a command file to perform the restore and recovery operation, and then execute the command file. The command file should do the following:

  a.

  Allocate a channel to the media manager.

  b.

  Restore a control file autobackup (see "Performing Recovery with a Backup Control File and No Recovery Catalog").

  c.

  Mount the restored control file.

  d.

  Catalog any backups not recorded in the repository with the CATALOG command. e. Restore the data files to their original locations. If volume names have changed, then run SET NEWNAME commands before the restore operation and perform a switch after the restore operation to update the control file with the new locations for the data files, as shown in the following example.

  f.

  Recover the data files. RMAN stops recovery when it reaches the log sequence number specified.

  RMAN> RUN

  {

  # Manually allocate a channel to the media manager

  ALLOCATE CHANNEL t1 DEVICE TYPE sbt;

  # Restore autobackup of the control file. This example assumes that you have

  # accepted the default format for the autobackup name.

  RESTORE CONTROLFILE FROM AUTOBACKUP;

  # The set until command is used in case the database

  # structure has changed in the most recent backups, and you want to # recover to that point in time. In this way RMAN restores the database # to the same structure that the database had at the specified time.

  ALTER DATABASE MOUNT;

  SET UNTIL SEQUENCE 1124 THREAD 1;

  RESTORE DATABASE;

  RECOVER DATABASE;

  }

  The following example of the RUN command shows the same scenario except with new file names for the restored data files:

  RMAN> RUN

  {

  # If you must restore the files to new locations,

  # use SET NEWNAME commands:

  SET NEWNAME FOR DATAFILE 1 TO '/dev/vgd_1_0/rlvt5_500M_1'; SET NEWNAME FOR DATAFILE 2

  TO '/dev/vgd_1_0/rlvt5_500M_2'; SET NEWNAME FOR DATAFILE 3 TO '/dev/vgd_1_0/rlvt5_500M_3';

  ALLOCATE CHANNEL t1 DEVICE TYPE sbt;

  RESTORE CONTROLFILE FROM AUTOBACKUP;

  ALTER DATABASE MOUNT;

  SET UNTIL SEQUENCE 124 THREAD 1;

  RESTORE DATABASE;

  SWITCH DATAFILE ALL; # Update control file with new location of data files.

  RECOVER DATABASE;

  }

  8. If recovery was successful, then open the database and reset the online logs: ALTER DATABASE OPEN RESETLOGS;

• Question 213:

  A user sends you an email with the following error message:

  create table idtable(id number)

  * ERROR at line 1:

  ORA-01116: error in opening database file 4

  ORA-01110: data file 4: '/oracle01/oradata/orcl/users01.dbf' ORA-27041: unable to open file Linux Error: 2:

  No such file or directory Additional information: 3

  You can choose from the following steps:

  a: Restore the missing database datafiles.

  b: Take the missing datafile offline.

  c: Shut down the database.

  d: Issue the recover tablespace USERS command.

  e: Issue the Startup Mount command to mount the database.

  f: Bring the USERS tablespace online.

  g: Issue the alter database open command.

  Which is the correct order of these steps in this case?

  A. b, a, d, f

  B. c, a, e, b, d, f, g

  C. c, e, d, g

  D. b, d, f

  E. e, d, g

  Correct Answer: A

  RMAN> repair failure;

  Strategy: The repair includes complete media recovery with no data loss Repair script: /u01/app/oracle/diag/rdbms/test0924/test0924/hm/reco_172511374.hm

  contents of repair script:

  # restore and recover datafile

  sql 'alter database datafile4 offline';

  restore datafile 4;

  recover datafile 4;

  sql 'alter database datafile 4 online';

• Question 214:

  You are using recovery Manager (RMAN) with a recovery catalog to backup up your production database.

  The backups and the archived redo log files are copied to a tape drive on a daily basis. The database was open and transactions were recorded in the redo logs. Because of fire in the building you lost your servers having the production database and the recovery catalog database. The archive log files generated after the last backup are intact on one of the remote locations. While performing a disaster recovery of the production database what is the next step that you must perform after restoring the data files and applying archived redo logs?

  A. Open the database in NORMAL mode

  B. Open the database in read-only mode

  C. Open the database in RESTRICTED mode

  D. Open the database with the RESETLOGS option

  Correct Answer: D

  Recovering the Database After a Disaster

  The procedure for disaster recovery is similar to the procedure for recovering the database with a backup control file in NOCATALOG mode. If you are restoring the database to a new host, then you should also review the considerations

  described in "Restoring a Database on a New Host". This scenario assumes that the Linux server on which your database was running has been damaged beyond repair. Fortunately, you backed up the database to Oracle Secure Backup

  and have the tapes available. The scenario assumes the following:

  Oracle Database is already installed on the new host. You are restoring the database to a new Linux host with the same directory structure as the old host.

  You have one tape drive containing backups of all the data files and archived redo logs through log 1124, and autobackups of the control file and server parameter file.

  You do not use a recovery catalog with the database.

  To recover the database on the new host:

  1.

  If possible, restore or re-create all relevant network files such as tnsnames.ora and listener.ora and a password file.

  2.

  Start RMAN and connect to the target database instance. At this stage, no initialization parameter file exists. If you have set ORACLE_SID and ORACLE_HOME, then you can use operating system authentication to connect as SYSDBA. For example, start RMAN as follows:

  % rman RMAN> CONNECT TARGET /

  3.

  Specify the DBID for the target database with the SET DBID command, as described in "Restoring the Server Parameter File".

  For example, enter the following command:

  SET DBID 676549873;

  4.

  Run the STARTUP NOMOUNT command.

  When the server parameter file is not available, RMAN attempts to start the instance with a dummy server parameter file.

  5.

  Allocate a channel to the media manager and then restore the server parameter file from autobackup.

  For example, enter the following command to restore the server parameter file from Oracle Secure Backup:

  RUN { ALLOCATE CHANNEL c1 DEVICE TYPE sbt; RESTORE SPFILE FROM AUTOBACKUP; }

  6.

  Restart the instance with the restored server parameter file. STARTUP FORCE NOMOUNT;

  7.

  Write a command file to perform the restore and recovery operation, and then execute the command file.

  The command file should do the following:

  a.

  Allocate a channel to the media manager.

  b.

  Restore a control file autobackup (see "Performing Recovery with a Backup Control File and No Recovery Catalog").

  c.

  Mount the restored control file.

  d.

  Catalog any backups not recorded in the repository with the CATALOG command. e. Restore the data files to their original locations. If volume names have changed, then run SET NEWNAME commands before the restore operation and perform a switch after the restore operation to update the control file with the new locations for the data files, as shown in the following example.

  f.

  Recover the data files. RMAN stops recovery when it reaches the log sequence number specified.

  RMAN> RUN { # Manually allocate a channel to the media manager ALLOCATE CHANNEL t1 DEVICE TYPE sbt; # Restore autobackup of the control file. This example assumes that you have # accepted the default format for the autobackup name.

  RESTORE CONTROLFILE FROM AUTOBACKUP;

  # The set until command is used in case the database

  # structure has changed in the most recent backups, and you want to # recover to that point in time. In this way RMAN restores the database # to the same structure that the database had at the specified time.

  ALTER DATABASE MOUNT;

  SET UNTIL SEQUENCE 1124 THREAD 1;

  RESTORE DATABASE;

  RECOVER DATABASE;

  }

  The following example of the RUN command shows the same scenario except with new file names for the restored data files:

  RMAN> RUN

  {

  # If you must restore the files to new locations,

  # use SET NEWNAME commands:

  SET NEWNAME FOR DATAFILE 1 TO '/dev/vgd_1_0/rlvt5_500M_1'; SET NEWNAME FOR DATAFILE 2

  TO '/dev/vgd_1_0/rlvt5_500M_2'; SET NEWNAME FOR DATAFILE 3 TO '/dev/vgd_1_0/rlvt5_500M_3';

  ALLOCATE CHANNEL t1 DEVICE TYPE sbt;

  RESTORE CONTROLFILE FROM AUTOBACKUP;

  ALTER DATABASE MOUNT;

  SET UNTIL SEQUENCE 124 THREAD 1;

  RESTORE DATABASE;

  SWITCH DATAFILE ALL; # Update control file with new location of data files.

  RECOVER DATABASE;

  }

  8. If recovery was successful, then open the database and reset the online logs: ALTER DATABASE OPEN RESETLOGS;

• Question 215:

  Your database is running in ARCHIVELOG mode. You have been taking backups of all the data files and control files regularly.

  You are informed that some important tables in the BILLING tablespace have been dropped on February 28, 2007 at 10.30 AM and must be recovered.

  You decide to perform an incomplete recovery using the following command:

  SQL> RECOVER DATABASE UNTIL TIME '2007-02-28:10:15:00';

  Identify the files that must be restored to recover the missing tables successfully.

  A. Restore the backup of all the data files.

  B. Restore the backup of all the data files and the control file.

  C. Restore the backup of only the data files that contain the dropped tables.

  D. Restore the backup of all the data files belonging to the tablespace containing the dropped tables.

  Correct Answer: A

  The tricky of answer is the command "RECOVER DATABASE", so that you must use "RESTORE DATABASE" to restore all the data files.

  If the recover command is "RECOVER DATAFILE", then the Answer D will be correct.

• Question 216:

  When performing a full database disaster recovery with RMAN, in what order would you execute these steps?

  a.

  Restore the control file from autobackups.

  b.

  Run the RMAN restore and recover command.

  c.

  Restore the database spfile from autobackups.

  d.

  Make the RMAN backup set pieces available.

  e.

  Open the database with the alter database open resetlogs command.

  f.

  Open the database with the alter database open command.

  A.

  a, b, c, d, e, f

  B.

  c, d, a, b, f

  C.

  d, c, a, b, f

  D.

  d, b, d, c, e

  E.

  d, c, a, b, e

  Correct Answer: E

• Question 217:

  Your production database is functional on the SHOST1 host. You are backing up the production database by using Recovery Manager (RMAN) with the recovery catalog. You want to replicate the production database to anther host, SHOST2, for testing new applications. After you ensured that the backups of the target database are accessible on the new host, what must you do to restore and recover the backup for the test environment?

  A. Restoring the control file from the backup by using the NOCATALOG option to restore, and recovering the data files

  B. Restoring the data files by using the NOCATALOG option and using the SET NEWNAME command to change the location

  C. Restoring the server parameter file from the backup by using the recovery catalog to restore,

  D. Restoring the data files from the backup by using the recovery catalog to recover the files, and using the SWITCH command to change the location.

  Correct Answer: A

  Refer to here:

  To restore the database on a new host:

  1.

  Ensure that the backups of the target database are accessible on the new host.

  2.

  Configure the ORACLE_SID on hostb.

  3.

  Start RMAN on hostb and connect to the target database without connecting to the recovery catalog.

  For example, enter the following command: % rman NOCATALOG RMAN> CONNECT TARGET /

  4.

  Set the DBID and start the database instance without mounting the database. For example, run SET DBID to set the DBID, then run STARTUP NOMOUNT:

  SET DBID 1340752057;

  STARTUP NOMOUNT

  RMAN fails to find the server parameter file, which has not yet been restored, but starts the instance with a "dummy" file. Sample output follows:

  startup failed: ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/net/hostb/oracle/dbs/inittrgta.ora' trying to start the Oracle instance without parameter files ... Oracle instance started

  5.

  Restore and edit the server parameter file.

  Allocate a channel to the media manager, then restore the server parameter file as a client-side parameter file and use the SET command to indicate the location of the autobackup (in this example, the autobackup is in /tmp):

  RUN { ALLOCATE CHANNEL c1 DEVICE TYPE sbt PARMS '...'; SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/tmp/%F'; RESTORE SPFILE TO PFILE '?/oradata/test/inittrgta.ora' FROM AUTOBACKUP; SHUTDOWN ABORT; }

  6.

  Edit the restored initialization parameter file.

  Change any location-specific parameters, for example, those ending in _DEST, to reflect the new directory structure. For example, edit the following parameters:

  -IFILE

  -LOG_ARCHIVE_DEST_1

  -CONTROL_FILES

  7.

  Restart the instance with the edited initialization parameter file.

  For example, enter the following command:

  STARTUP FORCE NOMOUNT PFILE='?/oradata/test/inittrgta.ora';

  8.

  Restore the control file from an autobackup and then mount the database. For example, enter the following command:

  RUN

  {

  ALLOCATE CHANNEL c1 DEVICE TYPE sbt PARMS '...';

  RESTORE CONTROLFILE FROM AUTOBACKUP;

  ALTER DATABASE MOUNT;

  }

  RMAN restores the control file to whatever locations you specified in the CONTROL_FILES initialization parameter.

  9.

  Catalog the data file copies that you copied in "Restoring Disk Backups to a New Host", using their new file names or CATALOG START WITH (if you know all the files are in directories with a common prefix easily addressed with a

  CATALOG START WITH command). For example, run:

  CATALOG START WITH '/oracle/oradata/trgt/';

  If you want to specify files individually, then you can execute a CATALOG command as follows:

  CATALOG DATAFILECOPY

  '/oracle/oradata/trgt/system01.dbf', '/oracle/oradata/trgt/undotbs01.dbf', '/oracle/oradata/trgt/cwmlite01.dbf',

  '/oracle/oradata/trgt/drsys01.dbf', '/oracle/oradata/trgt/example01.dbf', '/oracle/oradata/trgt/indx01.dbf', '/

  oracle/oradata/trgt/tools01.dbf', '/oracle/oradata/trgt/users01.dbf';

  10.

  Start a SQL*Plus session on the new database and query the database file names recorded in the control file.

  Because the control file is from the trgta database, the recorded file names use the original hosta file names. You can query V$ views to obtain this information. Run the following query in SQL*Plus:

  COLUMN NAME FORMAT a60 SPOOL LOG '/tmp/db_filenames.out' SELECT FILE# AS "File/Grp#", NAME FROM V$DATAFILE UNION SELECT GROUP#,MEMBER FROM V$LOGFILE; SPOOL OFF EXIT

  11.

  Write the RMAN restore and recovery script. The script must include the following steps:

  a.

  For each data file on the destination host that is restored to a different path than it had on the source host, use a SET NEWNAME command to specify the new path on the destination host. If the file systems on the destination system are set up to have the same paths as the source host, then do not use SET NEWNAME for those files restored to the same path as on the source host.

  b.

  For each online redo log that is to be created at a different location than it had on the source host, use SQL ALTER DATABASE RENAME FILE commands to specify the path name on the destination host. If the file systems on the destination system are set up to have the same paths as the source host, then do not use ALTER DATABASE RENAME FILE for those files restored to the same path as on the source host.

  c.

  Perform a SET UNTIL operation to limit recovery to the end of the archived redo logs. The recovery stops with an error if no SET UNTIL command is specified.

  d.

  Restore and recover the database.

  e.

  Run the SWITCH DATAFILE ALL command so that the control file recognizes the new path names as the official new names of the data files.

  Example 20-3 shows the RMAN script reco_test.rman that can perform the restore and recovery operation.

  Example 20-3 Restoring a Database on a New Host:

  RUN

  {

  # allocate a channel to the tape device

  ALLOCATE CHANNEL c1 DEVICE TYPE sbt PARMS '...';

  # rename the data files and online redo logs

  SET NEWNAME FOR DATAFILE 1 TO '?/oradata/test/system01.dbf'; SET NEWNAME FOR DATAFILE 2

  TO '?/oradata/test/undotbs01.dbf'; SET NEWNAME FOR DATAFILE 3 TO '?/oradata/test/cwmlite01.dbf';

  SET NEWNAME FOR DATAFILE 4 TO '?/oradata/test/drsys01.dbf'; SET NEWNAME FOR DATAFILE 5

  TO '?/oradata/test/example01.dbf'; SET NEWNAME FOR DATAFILE 6 TO '?/oradata/test/indx01.dbf'; SET

  NEWNAME FOR DATAFILE 7 TO '?/oradata/test/tools01.dbf'; SET NEWNAME FOR DATAFILE 8 TO '?/

  oradata/test/users01.dbf'; SQL "ALTER DATABASE RENAME FILE ''/dev3/oracle/dbs/redo01.log'' TO ''?/

  oradata/test/redo01.log'' ";

  SQL "ALTER DATABASE RENAME FILE ''/dev3/oracle/dbs/redo02.log'' TO ''?/oradata/test/redo02.log'' ";

  # Do a SET UNTIL to prevent recovery of the online logs SET UNTIL SCN 123456;

  # restore the database and switch the data file names RESTORE DATABASE;

  SWITCH DATAFILE ALL;

  # recover the database

  RECOVER DATABASE;

  }

  EXIT

  12.

  Execute the script created in the previous step.

  For example, start RMAN to connect to the target database and run the @ command:

  % rman TARGET / NOCATALOG

  RMAN> @reco_test.rman

  13.

  Open the restored database with the RESETLOGS option. From the RMAN prompt, open the database with the RESETLOGS option:

  ALTER DATABASE OPEN RESETLOGS;

  Caution:

  When you re-open your database in the next step, do not connect to the recovery catalog. Otherwise, the new database incarnation created is registered automatically in the recovery catalog, and the file names of the production database are

  replaced by the new file names specified in the script.

  14.

  Optionally, delete the test database with all of its files.

  Note:

  If you used an ASM disk group, then the DROP DATABASE command is the only way to safely remove the files of the test database. If you restored to non-ASM storage then you can also use operating system commands to remove the

  database.

  Use the DROP DATABASE command to delete all files associated with the database automatically. The following example deletes the database files:

  STARTUP FORCE NOMOUNT PFILE='?/oradata/test/inittrgta.ora'; DROP DATABASE;

  Because you did not perform the restore and recovery operation when connected to the recovery catalog, the recovery catalog contains no records for any of the restored files or the procedures performed during the test. Likewise, the control

  file of the trgta database is completely unaffected by the test.

• Question 218:

  Which of the following are valid until command options when attempting point-in-time recovery in RMAN? (Choose all that apply.)

  A. until time

  B. until change

  C. until sequence

  D. until SCN

  E. until commit

  Correct Answer: ACD

• Question 219:

  David managed to accidentally delete the datafiles for database called DSL. He called Heber and Heber tried to help but he managed to delete the control files of the database. Heber called Bill and Bill saved the day.

  They are using a recovery catalog for this database.

  What steps did Bill perform to recover the database and in what order?

  a.

  Restored the control file with the RMAN restore controlfile command.

  b.

  Mounted the DSL instance with the alter database mount command.

  c.

  Restored the datafiles for the DSL database with the RMAN restore command.

  d.

  Opened the DSL database with the alter database open resetlogs command.

  e.

  Recovered the datafiles for the DSL database with the RMAN recover command.

  f.

  Started the DSL instance.

  g.

  Connected to the recovery catalog with RMAN.

  A.

  a, b, c, d, e, f, g

  B.

  b, c, d, g, f, e, a

  C.

  g, f, a, b, c, e, d

  D.

  c, a, d, b, f, e, g

  E.

  g, f, a, b, e, c, d

  Correct Answer: C

  About Recovery with a Backup Control File

  If all copies of the current control file are lost or damaged, then you must restore and mount a backup trol file. You must then run the RECOVER command, even if no data files have been restored, and open the database with the

  RESETLOGS option. If some copies of the current control file are usable, however, then you can follow the procedure in "Responding to the Loss of a Subset of the Current Control Files" and avoid the recovery and RESETLOGS operation.

  When RMAN is connected to a recovery catalog, the recovery procedure with a backup control file is identical to recovery with a current control file.

  The RMAN metadata missing from the backup control file is available from the recovery catalog. The only exception is if the database name is not unique in the catalog, in which case you must use SET DBID command before restoring the

  control file.

  1.

  Start RMAN and connect to a target database.

  2.

  Start the target instance without mounting the database. RMAN>STARTUP NOMOUNT;

  3.

  Restore the control file

  RMAN> SET DBID 320066378; # (Optional) If the database name is not unique, you need to specify the DBID RMAN> RUN { SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'autobackup_format'; RESTORE CONTROLFILE FROM AUTOBACKUP; }

  4.

  Start the target instance with mounting the database. RMAN>STARTUP MOUNT;

  5.

  Restore the data files; RMAN>RESTORE DATABASE;

  6.

  Recover the database; RMAN>RECOVER DATABASE;

  7.

  Open the database with RESETLOGS option; RMAN> ALTER DATABASE OPEN RESETLOGS;

• Question 220:

  View the Exhibit to examine the error during the database startup. You open an RMAN session for the database instance. To repair the failure, you executed the following as the first command in the RMAN session:

  RMAN> REPAIR FAILURE;

  Which statement describes the consequence of the command?

  Exhibit:

  

  A. The command performs the recovery and closes the failures.

  B. The command only displays the advice and the RMAN script required for repair.

  C. The command produces an error because the ADVISE FAILURE command has not been executed before the REPAIR FAILURE command.

  D. The command executes the RMAN script to repair the failure and removes the entry from the Automatic Diagnostic Repository (ADR).

  Correct Answer: C