Practice 12-2: Restoring the Control File Overview
Practice 12-7: Recovering by Using an Encrypted Backup Overview
In this practice you will recover a lost data file by using an encrypted backup.
Assumptions
Practice 12-6 has been completed. An encryption wallet exists and a full database backup using transparent encryption has been created.
Two terminal windows are open and you are logged in as the oracle OS user. $LABS is the current directory. Environment variables are set for the orcl instance.
Tasks
1. Set up for this practice by executing the setup_12_07.sh script from the $LABS directory. In this script a new tablespace and user are created. The user creates a table and
populates it. A backup of the tablespace is performed and then the table is updated. The output is in the /tmp/setup.log file.
$ ./setup_12_07.sh $
2. Cause a failure in the database by executing the break_12_07.sh script from the $LABS directory. The output is in the /tmp/break.log file.
$ ./break_12_07.sh $
3. Attempt to start the database. Notice the error messages. $ sqlplus / as sysdba
Connected to an idle instance. SQL> startup
ORACLE instance started.
Total System Global Area 536870912 bytes Fixed Size 2926472 bytes Variable Size 281020536 bytes Database Buffers 247463936 bytes Redo Buffers 5459968 bytes Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3:
'/u01/app/oracle/oradata/orcl/sysaux01.dbf' SQL>
4. In another terminal window, use the LIST FAILURE and ADVISE FAILURE commands to diagnose the problem.
$ rman target "'/ as sysbackup'"
RMAN> LIST FAILURE;
using target database control file instead of recovery catalog Database Role: PRIMARY
List of Database Failures =========================
Failure ID Priority Status Time Detected Summary --- --- --- --- --- 62 HIGH OPEN 2014-12-18:07:00:34 One or more non-system datafiles are missing
RMAN> ADVISE FAILURE;
Database Role: PRIMARY
List of Database Failures =========================
Failure ID Priority Status Time Detected Summary --- --- --- --- --- 62 HIGH OPEN 2014-12-18:07:00:34 One or more non-system datafiles are missing
analyzing automatic repair options; this may take some time allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=250 device type=DISK analyzing automatic repair options complete
Mandatory Manual Actions ======================== no manual actions available
Optional Manual Actions
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. Practices for Lesson 12: Performing Recovery II
======================== Option Repair Description --- ---
1 Restore and recover datafile 3
Strategy: The repair includes complete media recovery with no data loss
Repair script:
/u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_3924442055.hm
RMAN>
5. Review the repair commands with the REPAIR FAILURE PREVIEW command. RMAN> repair failure preview;
Strategy: The repair includes complete media recovery with no data loss
Repair script:
/u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_3924442055.hm
contents of repair script:
# restore and recover datafile restore ( datafile 3 );
recover datafile 3;
sql 'alter database datafile 3 online'; RMAN>
6. Optionally, execute the repair and expect an error. RMAN> REPAIR FAILURE;
Strategy: The repair includes complete media recovery with no data loss
Repair script:
/u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_3924442055.hm
contents of repair script:
# restore and recover datafile restore ( datafile 3 );
recover datafile 3;
sql 'alter database datafile 3 online';
Do you really want to execute the above repair (enter YES or NO)? y
executing repair script
Starting restore at 2014-12-18:07:06:10 using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup/orcl/38pqd1lm_1_1
RMAN-00571:
=========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571:
=========================================================== RMAN-03002: failure of repair command at 12/18/2014 07:06:11 RMAN-03015: error occurred in stored script Repair Script ORA-19870: error while restoring backup piece
/u01/backup/orcl/38pqd1lm_1_1
ORA-19913: unable to decrypt backup ORA-28365: wallet is not open
RMAN>
7. Because the database was restarted and the encryption wallet is not configured to be an auto-login wallet, the encryption wallet must be opened before the recovery can begin. Logged in to SQL*Plus as SYSDBA or SYSKM, open the keystore and then exit.
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY secret;
keystore altered.
SQL> EXIT $
8. In your RMAN session, repair the failure and open the database. Notice that one of the pieces of the encrypted backup was used to restore the tablespace.
RMAN> REPAIR FAILURE;
Strategy: The repair includes complete media recovery with no data loss
Repair script:
/u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_3924442055.hm
contents of repair script:
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. Practices for Lesson 12: Performing Recovery II
Do you really want to execute the above repair (enter YES or NO)? y
executing repair script
Starting restore at 2014-12-18:07:14:55 using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup/orcl/38pqd1lm_1_1
channel ORA_DISK_1: piece handle=/u01/backup/orcl/38pqd1lm_1_1 tag=TRANSPARENT
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35 Finished restore at 2014-12-18:07:15:30
Starting recover at 2014-12-18:07:15:30 using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 5 is already on disk as file
/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2014_12_17/o1 _mf_1_5_b92ypv1r_.arc
archived log for thread 1 with sequence 6 is already on disk as file
/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2014_12_17/o1 _mf_1_6_b93z92j8_.arc
archived log for thread 1 with sequence 7 is already on disk as file
/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2014_12_17/o1 _mf_1_7_b93zb9t6_.arc
archived log for thread 1 with sequence 8 is already on disk as file
/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2014_12_18/o1 _mf_1_8_b94jv9x5_.arc
archived log file
name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2014_12_ 17/o1_mf_1_5_b92ypv1r_.arc thread=1 sequence=5
archived log file
name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2014_12_ 17/o1_mf_1_6_b93z92j8_.arc thread=1 sequence=6
media recovery complete, elapsed time: 00:00:28 Finished recover at 2014-12-18:07:15:59
sql statement: alter database datafile 3 online repair failure complete
Do you want to open the database (enter YES or NO)? y database opened
RMAN>
9. Use the LIST FAILURE command to verify that the failure has been repaired. Then exit RMAN.
RMAN> list failure; Database Role: PRIMARY
no failures found that match specification
RMAN> exit
Recovery Manager complete. $
Copyright © 2015, Oracle and/or its affiliates. All rights reserved. Practices for Lesson 12: Performing Recovery II