1 / 15

Recovering from Noncritical Losses

Recovering from Noncritical Losses. Objectives. After completing this lesson, you should be able to: Recover temporary tablespaces Recover a redo log group member Recover from a lost index Re-create the password file. Causes of File Loss. File loss can be caused by: User error

lael-malone
Download Presentation

Recovering from Noncritical Losses

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Recovering from Noncritical Losses

  2. Objectives After completing this lesson, you should be able to: • Recover temporary tablespaces • Recover a redo log group member • Recover from a lost index • Re-create the password file

  3. Causes of File Loss File loss can be caused by: • User error • Application error • Media failure

  4. Critical Versus Noncritical A noncritical file loss is one where the database can continue to function. • You fix the problem by taking one of these actions: • Create a new file. • Rebuild the file. • Recover the lost or damaged file.

  5. Losing a TEMPFILE SQL statements that require TEMP space to execute fail if one of the tempfiles is missing. SQL> select * from big_table order by 1,2,3,4,5,6,7,8,9,10,11,12,13; select * from big_table order by 1,2,3,4,5,6,7,8,9,10,11,12,13 * ERROR at line 1: ORA-01565: error in identifying file '/u01/app/oracle/oradata/orcl/temp01.dbf' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory

  6. Recovering from a TEMPFILE Loss Redo logfile 1A Redo logfile 1B SYSTEM USERS Parameter file Redo logfile 2A Redo logfile 2B SYSAUX INDEX Passwordfile UNDO TEMP Controlfiles

  7. Log Group Status: Review A redo log group has a status of one of the following values at any given time: • CURRENT: The LGWR process is currently writing redo data to it. • ACTIVE: It is no longer being written to, but it is still required for instance recovery. • INACTIVE: It is no longer being written to, and it is no longer required for instance recovery.

  8. Losing a Redo Log Group Member The alert log and the archiver process (ARCn) trace file record an error when a redo member file is missing. Errors in file /u01/app/oracle/admin/orcl/bdump/orcl_arc1_25739.trc: ORA-00313: open failed for members of log group 2 of thread 1 ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02b.log' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory

  9. Re-creating Redo Log Files Redo logfile 1A Redo logfile 1B SYSTEM USERS Parameter file Redo logfile 2A Redo logfile 2B SYSAUX INDEX Passwordfile UNDO TEMP Controlfiles SQL> ALTER DATABASE DROP LOGFILE MEMBER > '/u01/app/oracle/oradata/orcl/redo02b.log'; SQL> !rm /u01/app/oracle/oradata/orcl/redo02b.log SQL> ALTER DATABASE ADD LOGFILE MEMBER > '/u01/app/oracle/oradata/orcl/redo02b.log' > TO GROUP 2;

  10. Re-creating Redo Log Files

  11. Re-creating Indexes Use options to reduce the time it takes to create the index: • PARALLEL • NOLOGGING SQL> CREATE INDEX rname_idx 2 ON hr.regions (region_name) 3 PARALLEL 4;

  12. Authentication Methodsfor Database Administrators Remote database administration Local database administration Do you have a secure connection? Yes Yes Do you want to use OS authentication? Use OS authentication. Use a password file. No No

  13. Re-creating a Password Authentication File • Log in to the database by using OS authentication. • Set the REMOTE_LOGIN_PASSWORDFILE parameter to NONE and restart the database. • Re-create the password file by using orapwd. • Set REMOTE_LOGIN_PASSWORDFILE to EXCLUSIVE. • Add users to the password file and assign appropriate privileges to each user. • Restart the instance. $ orapwd file=$ORACLE_HOME/dbs/orapwORCL password=admin entries=5

  14. Summary In this lesson, you should have learned how to: • Recover temporary tablespaces • Recover a redo log group member • Recover from a lost index • Re-create the password file

  15. Practice Overview: Recovering from Lost TEMPFILE and Redo Log File This practice covers the following topics: • Starting the database with a missing tempfile • Creating a new temporary tablespace • Altering the default temporary tablespace for a database • Recovering from a lost online redo log member

More Related