.com Solutions Inc. - Installgen - Files - Oracle 9.2.0 for Mac OS X

Installgen - sol920 - Title Graphic

file: 39_sol_prod1_documentation_1.txt Features: Contains basic setup instructions for setting up the database along with the site configuration and troubleshooting commands. Used By: database DBA Copyright 2002 by .com Solutions Inc. This output file was created by Installgen version 1.38 on Sun Nov 10 14:20:50 2002. By .com Solutions Inc. www.dotcomsolutionsinc.net # ----------------------- Configuration Information: -------------------------- client name: .com Solutions Inc. Oracle CSI Number: 123456 Oracle Technical Support Phone#: 800-223-1711 Operating System: Solaris 8 Oracle Database Version: 9.2.0EE Oracle Home: /u01/v901 Oracle SID: prod1 DB_DOMAIN: east Database Blocksize: 16384 Memory Configuration: Small (< 512Mb) Hostname: blade1 IP Address: 10.1.0.16 Subnet Mask: 255.255.255.0 Router IP Address: 10.1.0.1 DNS Server IP Address: 10.1.0.17 UNIX group which owns Oracle sw: dba UNIX account which owns Oracle sw: oracle oracle UNIX account password: ab_12# Oracle SYS account password: n/a with 9i database Oracle SYSTEM account password: Wt_47i Oracle DBA account #1: admin Oracle admin account password: zx-vnm Oracle DBA account #2: thedba Oracle thedba account password: e2-76 Mount Device Names Points /u01 c0t0d0s0 Tablespace Sizes Paths Names system 300 /u01/prod1/system01.dbf tools 10 /u01/prod1/tools01.dbf users 10 /u01/prod1/users01.dbf rbs 20 /u01/prod1/rbs01.dbf indx 50 /u01/prod1/indx01.dbf temp 55 /u01/prod1/temp01.dbf xdb 10 /u01/prod1/xdb01.dbf drsys 85 /u01/prod1/drsys01.dbf Control Files; /u01/prod1/control01.ctl /u01/prod1/control02.ctl /u01/prod1/control03.ctl Redo Log Files: Group 1 /u01/prod1/redo01.log, /u01/prod1/redo01g1f2.log Group 2 /u01/prod1/redo02.log, /u01/prod1/redo02g2f2.log Group 3 /u01/prod1/redo03.log, /u01/prod1/redo03g3f2.log UDUMP Directory: /u01/udump CDUMP Directory: /u01/cdump BDUMP Directory: /u01/bdump Backup Type: RMAN - OEM OEM Server Hostname: ale OEM Server IP Address: 10.1.0.13 OEM Server SID: oem1 OEM Server Dbdomain: world OEM Tablespace Name: oemtbs host3 OEM Tablespace Size(Mb): 10 OEM Tablespace Filename: /u02/prod1/oemtbs01.dbf OEM Repository Owner Account: oemuser OEM Repository Owner Account Password: e4-43 Additional OEM Registered Hosts: host2, host3 Additional OEM Registered Database Instances: prod2, prod3 RMAN Tablespace Name: rmantbs RMAN Tablespace Size(Mb): 10 RMAN Tablespace Filename: /u02/prod1/rmantbs01.dbf RMAN Repository Owner Account: rman RMAN Repository Owner Account Password: rt-ut Backup Directory: /backup Backup Time(24 hour clock): 4:03 Archivelog: Yes Archivelog Directory: /archive Standby Database Hostname: ultra1 Standby Database IP Address: 10.1.0.3 Standby Database Archivelog Delay: 4 hrs Options Installed: Oracle JServer Intermedia/Oracle Text (9i) SQL Plus Help SGA Settings: Processes: 50 Log Buffer Size: 3000 DB Block Buffer Size (DB Cache Size in 9i): 100 * 16384 = 1638400 Shared Pool Size: 150000000 Java Pool Size: 150000000 Additional Documentation Files: Spreadsheet of tasks performed: 40_sol_prod1_tasklist_sheet_1.slk SID Overview Diagram: prod1_overview_diagram_1.svg Backup Setup Timeline: 43_sol_prod1_backup_timeline_1.slk Disaster Recovery Text File (produced nightly): /backup/prod1_disaster_recovery.txt Note: *.svg (standardize vector graphics) format files are readable and editable with Jasc WebDraw, and viewable with web browsers containing the proper plugin. Configured Oracle NLS Language parameters are available from: select * from V$NLS_PARAMETERS; # ----------------------- Installation Process: -------------------------- 1) Open the Installgen application. 2) Enter appropriate database creation parameters in the General, Backup/Recovery, Files/Tablespaces, Options and SGA tabs. Note: For 9.2.0 databases the XDB feature is added whenever the JServer feature is installed. If an XDB tablespace has not been configured, it will be automatically added at the same mount point as the USERS tablespace. 3) Create a folder or directory which will hold the files created by Installgen. 4) Verify that the Output directory field on the General tab is correct. 5) Select Save As from the File menu to save the configuration information for this particular database server. 6) Press the Generate button to generate the setup scripts/files. 7) Log into the blade1 server, then execute the commands in the 1_sol_920_prepinstall_1.sh script as the root user to create additional UNIX accounts. 8) Insert the oracle_build1 CD into the CD-ROM drive of the Windows system where Installgen was run. 9) Execute the 2_sol_920_prepinstall_1.bat batch file from the Installgen build directory of the Windows system. This script will open an ftp session to the Sun server and ftp the additional Solaris 8 utilities and all of the files from the build directory created by Installgen. The ftp session is opened using the newly created oracle owner UNIX account named oracle and the files will be placed into the /export/oracle directory. 10) Make the 3_sol_920_prepinstall_1.sh script executable with the command: chmod +x 3_sol_920_prepinstall_1.sh. 11) Execute the 3_sol_920_prepinstall_1.sh on the database server to set the file permissions on the rest of the files in the /export/oracle directory. 12) Execute the 4_sol_920_prepinstall_1.sh script as root on the database server. This script checks for the existence of various Solaris utilities including top, less, Perl5, and ssh. Then the script requests permission to install each of these utilities. The script creates and installs the dbora startup script, oratab file, creates mount point directories to contain the Oracle datafiles, sets permissions/ownership of these directories. Permission is requested and if granted, the script creates/installs vfstab, defaultrouter, hosts, system, profile files, creates/installs /opt/server_scripts and rotateoraclefiles.sh script for rotating/removing log files, and creates/installs the sshd startup script file. 13) Insert the 1st Oracle installation CD into the Sun server, then execute the 5_sol_920_prep_copyoracle_cds_1.sh script as root. This script copies the contents from each of the three Oracle installation CDs into the /export/oracle directory, then creates NFS shares of the directories so that the software can be copied to other servers within the network. 14) Transfer the 6_sol_920_prep_copyoracle_nfs_1.sh script to additional Sun servers where Oracle will be installed (such as the OEM server). Execute the 6_sol_920_prep_copyoracle_nfs_1.sh on each of those servers after executing the appropriate versions of the 1_sol_920_prepinstall_1.sh, 2_sol_920_prepinstall_1.bat 3_sol_920_prepinstall_1.sh, and 4_sol_920_prepinstall_1.sh scripts for the server. Note: OEM servers should use the 12_sol_920_oem_dbora_1.sh in place of the 11_sol_920_dbora_1.sh file renamed to /etc/init.d/dbora to start up the database and the OMS. 15) Restart the Sun server to permit the kernel modifications in the /etc/system file to become effective. 16) Execute the 7_sol_ora_install_1.sh script to initiate the installation process using the Oracle Java based installer on the server. 17) Select "y" when prompted by the 7_sol_ora_install_1.sh script to start the process of building the database using script 8_sol_920_db_build_1.sh. This process may take several hours, depending upon the number of features installed and the performance of the server. 18) Execute the 9_sol_920_ora_post_install_1.sh script to create Oracle dba accounts, change default database passwords, apply the latest patchfile to the database, and configure backup and recovery features. 19) Perform a full backup of the database to test the backup process (and a full export too if possible). creates RMAN Tablespace Name rmantbs creates RMAN Repository Owner Account rman grants privileges to rman account creates OEM Tablespace Name oemtbs host3 creates OEM Repository Owner Account oemuser grants privileges to oemuser account creates OEM repository using Enterprise Manager Configuration Assistant (EMCA) starts the Oracle Management Server prompts for starting the Oracle Intelligent Agent on each database server prompts for using the Enterprise Manager Console to create administrator accounts prompts for using the Enterprise Manager Console to discover each database creates RMAN repository in oem1 database instance prompts for scheduling of the following OEM jobs: prod1_rman_oem_backup_job_1.sh - Nightly RMAN OEM full database backup job at 4:03 prod1_export_full_db_1.sh - Full Oracle Database Export nightly at 11:50PM prod1_gather_stats_day_job_1.sh - Gather daytime Oracle CBO stats Mondays at 9:00AM prod1_gather_stats_night_job_1.sh - Gather nightly batch Oracle CBO stats Mondays at 9:00PM prod1_import_stats_day_job_1.sh - Import daytime Oracle CBO stats weekdays daily at 5:00AM prod1_import_stats_night_job_1.sh - Import nightly Oracle CBO stats weekdays daily at 6:00PM prod1_schema_stats_job_1.sh - Gather all schema stats Fridays 9:00PM creates DBMS.STATS table for gathering database stats # ----------------------- Standby Database Installation Process: -------------------------- 1) Open the Installgen application and fill in the database parameters as would be done to create any new database. Fill in all of the parameters for the primary database. It is important for the Standby SID to be different from the primary database SID in order for the databases to be accessed via different tnsnames.ora entries. 2) If a media manager is being used for RMAN backups, the standby server will need access to the tape drive thru the media manager software. The RMAN Channel type field is used for the media manager device specifier. Otherwise, the /backup location on each server will need to be large enough to store a full backup of the database. 3) Press the Generate button to create the installation scripts. Save the configuration information for the primary database. 4) Change the IP Address, and hostname parameters, but leave the tablespaces and pathnames the same. Save this configuration for the standby database, then generate a set of scripts which will be used for installing the Oracle software and building an instance on the standby server. The standby server must use the same Oracle version, patchlevel, OS and directory structure as the primary database. Edit the scripts if needed to change the default patchlevel which will be installed. Use the scripts generated for the standby databse to create an instance on the standby server and apply the latest patchset to the instance. This instance will be replaced during the creation of the standby instance - it is only created in order to install and patch the software. The remainder of these instructions make use of the first set of scripts which were created with info for the primary database. 5) The following files are used for the standby database setup after Oracle software has been installed and patched on the standby server: 77_sol_920_prod1_primary_standbysetup_1.pl 78_sol_920_prod1_standby_standbysetup_1.pl 79_prod1_standby_status_1.pl 80_sol_prod1_standbydb_change_role_1.pl 70_prod1_primary_tnsnames_1.ora 71_prod1_failover_tnsnames_1.ora 72_prod1_client_taf_tnsnames_1.ora 75_sol_920_prod1_primary_init_1.ora 49_listener_ora_1.ora 74_sol_prod1_standby_listener_1.ora 76_sol_920_prod1_standby_init_1.ora 81_prod1_standby_startup_script_1 73_sol_prod1_standby_etc_profile_1.sh 10_sol_etc_profile_1.sh 11_sol_920_dbora_1.sh 46_sol_oratab_1.txt 86_sqlnet_1.ora 28_sol_oratab_standby_1.txt 88_sol_standby_primary_listener_1.ora 89_sol_primary_standby_listener_1.ora (It is generally recommended that all of the generated files be copied to the primary server in case they are needed.) 6) Run the 77_sol_920_prod1_primary_standbysetup_1.pl Perl script on the primary server. Follow the prompts. 7) You will be prompted to copy 78_sol_920_prod1_standby_standbysetup_1.pl to the /export/oracle/ directory on the standby server. Run the 78_sol_920_prod1_standby_standbysetup_1.pl Perl script, and follow the prompts. 8) An RMAN backup will be done on the primary server, if requested. RMAN will limit its I/O consumption to a rate of 1500K per second in order to allow the backup to be performed with the primary database operating in production mode. This parameter can be modified within script 77_sol_920_prod1_primary_standbysetup_1.pl if necessary. The primary server's init.ora parameters are modified dynamically without bouncing the instance. 9) Once the RMAN backup has completed, the configuration, standby controlfile, backup and archived redo log files are copied to the standby server via a network share. This shared folder on the standby server needs to correspond to the directory where RMAN backed up the primary database - unless a media manager was used to back the database directly to tape. 10) On the standby server, the archived redo log files will be copied to the /archive directory so that they will be available for the RMAN duplicate standby database process. The primary and standby versions of the tnsnames.ora, listener.ora and other configuration files will be copied into the appropriate locations. The 81_prod1_standby_startup_script_1 script will be installed on the standby server. The 81_prod1_standby_startup_script_1 script renames the Oracle Alert log for the instance every time the server is restarted. This task is done during system startup because the file is in use while the database is running. The 81_prod1_standby_startup_script_1 script automatically starts up the standby instance in managed recovery mode every time the server is restarted. The OracleStandbyStartupPROD1.log file is written to the /opt/server_scripts directory each time the 81_prod1_standby_startup_script_1 script runs. 11) The standby server is started in nomount mode in preparation for the RMAN duplicate standby database procedure. 12) RMAN is run on the primary server via an auxiliary channel with a connection to the standby server. RMAN creates standby controlfiles on the standby server with the same names and locations as the primary server. RMAN then creates datafiles with the same names and paths as the tablespaces in the primary database. RMAN then applies all of the archivelog files up to the point in time when the standby controlfile was created. Note: If RMAN fails with an ORA-6025 error, the 80_sol_prod1_standbydb_change_role_1.pl Perl program may be used to copy and apply missed archivelog files from the primary server to the standby server. 13) The standby server instance is then started in managed recovery mode. 14) During the standby server setup process the 13_sol_rotateoraclefiles_1.sh script will be configured to run via a CRON job at 11:55PM. This script removes old logfiles, archivelog and backup files to prevent the disk from filling up. (Since the standby database is identical to the primary database, it may be configured for RMAN backups to reduce the impact on the primary database. RMAN backups done in this manner must use the controlfile to store backup information without using an RMAN repository within an OEM/OMS server.) 15) Install the 72_prod1_client_taf_tnsnames_1.ora file as tnsnames.ora on each client computer which will be connecting to the primary database. This tnsnames.ora file is configured with transparent application failover to allow the client to automatically attempt to connect to the standby database if the primary database has failed. 16) Use the prod1_standbydb_change_role.pl perl script to manage the standby configuration. The prod1_standbydb_change_role.pl perl script provides the following features: primary to standby switchover primary to standby failover standby switchover to primary standby failover to primary standby to read-only read-only to standby # ----------------------- Database Recovery Scenarios: -------------------------- Summary of Scenarios: Scenario 1 - Loss of INACTIVE Online Redo Log Group Scenario 2 - Loss of CURRENT Online Redo Log Group Scenario 3 - Loss of Control Files Scenario 4 - Loss of Media Scenario 5 - Loss of an Online Undo Segment Datafile (Open or Closed Database) Scenario 6 - Loss of a SYSTEM tablespace Datafile Scenario 7 - Loss of a Non-System, Non-Rollback Segment Datafile Scenario 8 - Recover from User Errors Scenario 9 - Failure during Online Backup Scenario 10 - Missing Data File Scenario 11 - Loss of a Datafile and Missing Archive Log File Scenario 12 - Loss of Non-Essential Datafile When Database is Down Scenario 13 - Recover a Lost Datafile with No Backup Scenario 14 - Missing Mirrored Online Redo Log Files Scenario 15 - Loss of a Control File and Read-Only Tablespace # -------------------------------------------------------------------------------- # ----------------------- Scenario 1 - Loss of INACTIVE Online Redo Log Group Archivelog mode = Yes Situation: Both the primary and the multiplexed redo log files in an INACTIVE redo log group have been destroyed. # -------------------------------------------------------------------------------- ------------------------- Preferred Recovery Method - using RMAN backup/restore with a recovery catalog ------------------------- Note: This Recovery Method expects that the database was previously backed up with RMAN using an RMAN catalog located within another database server. 1) Use the OEM Console to log into the OEM repository using an account which has DBA privileges. 2) Select the database which needs recovered from the list of databases at the left side of the OEM Console window. As long as the Oracle Intelligent Agent is running, it should be possible to open the affected database item in the window, but the instance configuration will show that the database is in shutdown state. 3) With the database still selected, select the menu item Object/Startup... then select the NOMOUNT radio button to startup the database to the NOMOUNT state. 4) Select the menu item Tools/Database Tools/Backup Management/Recovery... Select "Entire Database", because there is no selection for redo log file recovery. Do not select the "Perform recovery without restoring the datafiles." checkbox. Click the "Next" button. 5) It is necessary to perform an incomplete recovery because a redo log file was lost,therefore you need to pick the recovery date/time on the "Restore Until" screen. Check the /u01/bdump/prod1ALRT.LOG file to find out when the first error was reported by the LGWR process when it could not access the deleted redo log files. Pick a time just before the failure occurred. Click the "Next" button. 6) On the "Rename" screen, just click the "Next" button without renaming any datafiles. 7) Make sure that the same RMAN channel was selected for this restore, as was previously used for performing the RMAN backup. Otherwise, RMAN will not be able to locate the backup files to complete the restore. Click the "Finish" button. Click "Ok" to acknowledge that the job has been submitted. Monitor the job completion by clicking on the "Jobs" icon in the left window. 8) After the restore/recovery job has completed, click on the database Instance/Configuration icon in the left window of the OEM Console window. Click the "Open" radio button, then click on the "Apply" button near the bottom of the window. Select the "Resetlogs" startup method in the window which pops up since online redo log files were lost/recovered. Click the "Open" button. 9) Prior to performing the next RMAN backup, it will be necessary to reset the database information in the RMAN catalog. Select the menu item Tools/Database Tools/Backup Management/Maintenance... Click the "Next" button. Click the "Reset database" radio button. Click the "Next" button. Click the "Finish" button. Click the "Ok" button on the next 2 windows which pop up. 10) Perform another full RMAN database backup since the previous backups are now unusable due to the database now having been set to a different database incarnation number. 11) Once the RMAN backup has completed, the database will then be ready for production use. ------------------------- Alternate Recovery Method 1 - RMAN backup/restore - using RMAN without a recovery catalog (using controlfile info) ------------------------- 1) The database instance will likely fail with ORA-03113 end-of-file on communications channel if the use of a damaged redo log group is attempted. This error may occur under normal operation of the database, or after manually issuing the command: ALTER SYSTEM SWITCH LOGFILE. Subsequent attempts to issue commands within sqlplus may result in ORA-24324. 2) Checking the /u01/bdump/prod1ALRT.LOG file will show ORA-0313 errors since the files in the redo log group can't be found. Verify that the data from the destroyed redo log file group was archived. SELECT * FROM V$LOG; The "ARC" column should contain "YES". If not, then proceed to troubleshooting with recovery scenario #2. 3) Issue the following command to determine if there are any offline datafiles which require data from the unarchived redo log files which were lost in order to be brought online. SELECT NAME,STATUS FROM V$DATAFILE; 4) Look for errors within the /u01/bdump/prod1ALRT.LOG file to determine the time when the redo log files were destroyed. Restore/recover the database with RMAN to a point in time, Sequence or SCN prior to the time of the failure. Since an online redo log file group has been lost, an incomplete recovery of the database will be required, and some data will be lost. For the purpose of this example, the time of the failure was 2:18PM, therefore the restore/recovery will be performed up to the time of 2:15PM. Use RMAN to restore and perform an incomplete recovery of the database: rman target / startup mount RUN { ALLOCATE CHANNEL ch1 TYPE Disk; SET UNTIL TIME = "to_date('2002/02/10 14:15:00','YYYY/MM/DD HH24:MI:SS')"; RESTORE DATABASE; RECOVER DATABASE; ALTER DATABASE OPEN RESETLOGS; } 5) At this time, RMAN will have recovered all of the datafiles and restored the database back to 2:15PM. The destroyed redo log files will have also been re-created. 6) Since the database was opened with resetlogs, it is necessary to perform another full backup of the database because all previous backups will be invalid. rman target / RUN { ALLOCATE CHANNEL ch1 TYPE Disk maxpiecesize = 1900M; BACKUP FULL DATABASE INCLUDE CURRENT CONTROLFILE FORMAT '/backup/%d_datafile_%s_%p.bak' TAG 'prod1_datafile_daily'; } RUN { ALLOCATE CHANNEL ch1 TYPE Disk MAXPIECESIZE = 1900M; BACKUP ARCHIVELOG ALL DELETE ALL INPUT FORMAT '/archive/%d_archivelog_%s_%p.bak' TAG 'prod1_archivelog_daily'; } QUIT Note: If the point in time recovery is attempted, and the recovery point in time is specified incorrectly, it is not just a simple matter of running the RMAN restore/recovery command again with another time specification. It will be necessary to change the database incarnation to its previous value before attempting the RMAN restore/recovery again. The database incarnation value must match the database incarnation value which was being used within the database at the time the RMAN backup was performed. This procedure can only be done when using RMAN with a recovery catalog instead of using the controlfile as the RMAN catalog. 7) The database is now available for production use. ------------------------- Alternate Recovery Method 2 - incomplete recovery using archivelog files ------------------------- 1) The database instance will likely fail with ORA-03113 end-of-file on communications channel if the use of a damaged redo log group is attempted. This error may occur under normal operation of the database, or after manually issuing the command: ALTER SYSTEM SWITCH LOGFILE. Subsequent attempts to issue commands within sqlplus may result in ORA-24324. 2) Checking the /u01/bdump/prod1ALRT.LOG file will show ORA-0313 errors since the files in the redo log group can't be found. Verify that the data from the destroyed redo log file group was archived. SELECT * FROM V$LOG; The "ARC" column should contain "YES". If not, then proceed to troubleshooting with recovery scenario #2. 3) Mount the database. sqlplus /nolog CONN / AS SYSDBA STARTUP MOUNT PFILE='/u01/v901/database/initprod1.ora' 4) Check the V$LOG view to determine if the file has been archived. SELECT * FROM V$LOG; Find the current log sequence number (for this example it is 2). 5) Recover the database until cancel (cancel before getting to log sequence 3). RECOVER DATABASE UNTIL CANCEL CANCEL 6) Open the database with resetlogs, because incomplete recovery was performed. ALTER DATABASE OPEN RESETLOGS; 7) Add a new redo log group with 2 members by using the information in the above Configuration Information section: ALTER DATABASE ADD LOGFILE GROUP 5 ('/u01/prod1/redo05g5f1.log','/u02/prod1/redo05g5f2.log') SIZE 10M; 8) Drop the damaged redo log file group: ALTER DATABASE DROP LOGFILE GROUP 4; 9) Perform a full backup of the database. # -------------------------------------------------------------------------------- # ----------------------- Scenario 2 - Loss of CURRENT Online Redo Log Group Archivelog mode = Yes Situation: Both the primary and the multiplexed redo log files in the CURRENT redo log group have been destroyed. # -------------------------------------------------------------------------------- ------------------------- Preferred Recovery Method - using RMAN backup/restore with a recovery catalog ------------------------- Note: This Recovery Method expects that the database was previously backed up with RMAN using an RMAN catalog located within another database server. 1) Use the OEM Console to log into the OEM repository using an account which has DBA privileges. 2) Select the database which needs recovered from the list of databases left window of the OEM Console window. As long as the Oracle Intelligent Agent is running, it should be possible to open the affected database in the window, but the instance configuration will show that the database is in shutdown state. 3) With the database still selected, select the menu item Object/Startup... then select the NOMOUNT radio button to startup the database to the NOMOUNT state. 4) Select the menu item Tools/Database Tools/Backup Management/Recovery... Click the "Next" button. Select "Entire Database", because there is no selection for redo log file recovery. Do not select the "Perform recovery without restoring the datafiles." checkbox. Click the "Next" button. 5) It is necessary to perform an incomplete recovery because a redo log file was lost, therefore you need to pick the recovery date/time on the "Restore Until" screen. Check the /u01/bdump/prod1ALRT.LOG file to find out when the first error was reported by the LGWR process when it could not access the deleted redo log files. Pick a time just before the failure occurred. Click the "Next" button. 6) On the "Rename" screen, just click the "Next" button without renaming any datafiles. 7) Make sure that the same RMAN channel is selected for this restore, as was previously used for performing the RMAN backup. Otherwise, RMAN will not be able to locate the backup files to complete the restore. Click the "Finish" button. Click "Ok" to acknowledge that the job has been submitted. Monitor the job completion by clicking on the "Jobs" icon in the left window. 8) After the restore/recovery job has completed, click on the database Instance/Configuration icon at the left side of the OEM Console window. Click the "Open" radio button, then click on the "Apply" button near the bottom of the window. Select the "Resetlogs" startup method in the window which pops up since online redo log files were lost/recovered. Click the "Open" button. 9) Prior to performing the next RMAN backup, it will be necessary to reset the database information in the RMAN catalog. Select the menu item Tools/Database Tools/Backup Management/Maintenance... Click the "Next" button. Click the "Reset database" radio button. Click the "Next" button. Click the "Finish" button. Click the "Ok" button on the next 2 windows which pop up. 10) Perform another full RMAN database backup since the previous backups are now unusable due to the database now having been set to a different database incarnation number. 11) Once the RMAN backup has completed, the database will then be ready for production use. ------------------------- Alternate Recovery Method 1 - RMAN backup/restore - using RMAN without a recovery catalog (using controlfile info) ------------------------- 1) The database instance will likely fail with ORA-03113 end-of-file on communications channel if the use of a damaged redo log group is attempted. This error may occur under normal operation of the database, or after manually issuing the command: ALTER SYSTEM SWITCH LOGFILE; Subsequent attempts to issue commands within sqlplus may result in ORA-24324. 2) Checking the /u01/bdump/prod1ALRT.LOG file will show ORA-0313 errors since the files in the redo log group can't be found. 3) Verify that the data from the destroyed redo log file group was archived. SELECT * FROM V$LOG; The "ARC" column should contain "YES". 4) Issue the following command to determine if there are any offline datafiles which require data from the unarchived redo log files which were lost in order to be brought online. SELECT NAME,STATUS FROM V$DATAFILE; 5) Shut down the instance. SHUTDOWN IMMEDIATE 6) Look for errors within the /u01/bdump/prod1ALRT.LOG file to determine the time when the redo log files were destroyed. Restore/recover the database with RMAN to a point in time, Sequence or SCN prior to the time of the failure. Since an online redo log file group has been lost, an incomplete recovery of the database will be required, and some data will be lost. For the purpose of this example, the time of the failure was 2:18PM, therefore the restore/recovery will be performed up to the time of 2:15PM. Use RMAN to restore and perform an incomplete recovery of the database: rman target / STARTUP MOUNT RUN { ALLOCATE CHANNEL ch1 TYPE Disk; SET UNTIL TIME = "to_date('2002/02/10 14:15:00','YYYY/MM/DD HH24:MI:SS')"; RESTORE DATABASE; RECOVER DATABASE; ALTER DATABASE OPEN RESETLOGS; } 7) At this time, RMAN will have recovered all of the datafiles and restored the database back to 2:15PM. The destroyed redo log files will have also been re-created. 8) Since the database was opened with resetlogs, it is necessary to perform another full backup of the database because all previous backups will be invalid. rman target / RUN { ALLOCATE CHANNEL Disk TYPE ch1 MAXPIECESIZE = 1900M; BACKUP FULL DATABASE INCLUDE CURRENT CONTROLFILE FORMAT '/backup/%d_datafile_%s_%p.bak' TAG 'prod1_datafile_daily'; } RUN { ALLOCATE CHANNEL Disk TYPE ch1 MAXPIECESIZE = 1900M; BACKUP ARCHIVELOG ALL DELETE ALL INPUT FORMAT '/archive/%d_archivelog_%s_%p.bak' TAG 'prod1_archivelog_daily'; } QUIT Note: If the point in time recovery is attempted, and the recovery point in time is specified incorrectly, it is not just a simple matter of running the RMAN restore/recovery command again with another time specification. It will be necessary to change the database incarnation to its previous value before attempting the RMAN restore/recovery again. The database incarnation value must match the database incarnation value which was being used within the database at the time the RMAN backup was performed. This procedure can only be done when using RMAN with a recovery catalog instead of using the controlfile as the RMAN catalog. 9) The database is now available for production use. ------------------------- Alternate Recovery Method 2 - incomplete recovery using archivelog files ------------------------- 1) The database instance will likely fail with ORA-03113 end-of-file on communications channel if the use of a damaged redo log group is attempted. This error may occur under normal operation of the database, or after manually issuing the command: ALTER SYSTEM SWITCH LOGFILE; Subsequent attempts to issue commands within sqlplus may result in ORA-24324. 2) Checking the /u01/bdump/prod1ALRT.LOG file will show ORA-0313 errors since the files in the redo log group can't be found. Verify that the data from the destroyed redo log file group was archived. SELECT * FROM V$LOG; The "ARC" column should contain "YES". 3) Mount the database. sqlplus /nolog CONN / AS SYSDBA STARTUP MOUNT PFILE='/u01/v901/database/initprod1.ora' 4) Check the V$LOG view to determine if the file has been archived. SELECT * FROM V$LOG; Find the current log sequence number (for this example it is 2). 5) Recover the database until cancel (cancel before getting to log sequence 3). RECOVER DATABASE UNTIL CANCEL CANCEL 6) Open the database with resetlogs, because incomplete recovery was performed. ALTER DATABASE OPEN RESETLOGS; 7) Add a new redo log group by using the information in the above Configuration Information section (in case the drive was lost containing the original redo log files): ALTER DATABASE ADD LOGFILE GROUP 5 ('/u01/prod1/redo05g5f1.log','/u02/prod1/redo05g5f2.log') SIZE 10M; 8) Drop the damaged redo log file group: ALTER DATABASE DROP LOGFILE GROUP 4; 9) Perform a full backup of the database. # -------------------------------------------------------------------------------- # ----------------------- Scenario 3 - Loss of Control Files Archivelog mode = Yes Situation: All controlfiles for the database have been destroyed. # -------------------------------------------------------------------------------- ------------------------- Preferred Recovery Method - using existing binary control file ------------------------- 1) Shut down the database. 2) Copy any one of the existing control files into the location of the missing control file. (Of course this will not be an option if every one of the control files has been lost.) 3) Rename the copy of the control file with the name of the missing control file name. If necessary, refer to the /u01/v901/database/initprod1.ora file or the Configuration Information section listed above for the names and locations of the control files. EXAMPLE: If control file /u01/prod1/control01.ctl is missing: sqlplus /nolog CONN / AS SYSDBA SHUTDOWN IMMEDIATE host copy /u01/prod1/control02.ctl /u01/prod1/control01.ctl STARTUP EXIT; ------------------------- Alternate Recovery Method 1 - using RMAN restore with a recovery catalog ------------------------- If any one of the binary control files still exist, do not perform an RMAN restore. See the previous "Preferred Recovery Method - using existing binary controlfile" section above. Only use RMAN to restore lost control files if all binary copies of the control files have been lost. 1) Make sure that the database has been shutdown. sqlplus /nolog CONN / AS SYSDBA SHUTDOWN IMMEDIATE QUIT 2) Execute RMAN from the server which needs the restore, connect to the recovery catalog in the OMS server then perform the restore of only the controlfiles. rman target / CONNECT CATALOG rman/rt-ut@oem1.world RUN { STARTUP NOMOUNT; ALLOCATE CHANNEL ch1 TYPE Disk; RESTORE CONTROLFILE; SQL 'ALTER DATABASE MOUNT'; RECOVER DATABASE; SQL 'ALTER DATABASE OPEN RESETLOGS'; } 3) Prior to performing the next RMAN backup, it will be necessary to reset the database information in the RMAN catalog. Using the OEM Console application, select the menu item Tools/Database Tools/Backup Management/Maintenance... Click the "Next" button. Click the "Reset database" radio button. Click the "Next" button. Click the "Finish" button. Click the "Ok" button on the next 2 windows which pop up. 4) Perform another full RMAN database backup since the previous backups are now unusable due to the database now having been set to a different database incarnation number. 5) Once the RMAN backup has completed, the database will then be ready for production use. ------------------------- Alternate Recovery Method 2 - using control file backup to trace file ------------------------- 1) Copy the trace file script from the /backup directory to rebuild the control file. For the purposes of this example, the file will be renamed as prod1_rebuild_controlfile.sql. 2) Edit the trace file to remove uneeded uncommented characters and to change the '#' comment characters to '--' characters to prevent SQL Plus from generating errors. Since this is an Oracle 9.2.0 database, the trace file contains 2 sections. The second section performs an OPEN RESETLOGS of the database - which is required for this scenario. Remove the 1st half of the trace file until you get to the text: "# Set #2. RESETLOGS case". Save the edited trace file as: prod1_rebuild_controlfile.sql. 3) Run the modified trace file script to recreate the control files. (Note: If the database structure has not changed since it was originally created, it may be possible to run the 37_sol_prod1_rebuild_controlfile_1.sql script to re-build all of the control files.) sqlplus /nolog CONN / AS SYSDBA SHUTDOWN IMMEDIATE @prod1_rebuild_controlfile.sql QUIT Note: The Oracle generated tracefile may contain a syntax error for the line which adds the temp tablespace. You may use example within the 37_sol_prod1_rebuild_controlfile_1.sql script for the correct syntax. 4) The database is now ready for production use. It is recommended that as a minimum the controlfiles should be backed up. Ideally, it would be a good idea to back up the entire database. ------------------------- Alternate Recovery Method 3 - using 37_sol_prod1_rebuild_controlfile_1.sql script Note: Only use this recovery method if the structure of the database has not changed since it was originally created. ------------------------- 1) Make sure that the database is shutdown. 2) Retrieve the 37_sol_prod1_rebuild_controlfile_1.sql file to rebuild the control file. 3) Use sqlplus to execute 37_sol_prod1_rebuild_controlfile_1.sql to recreate the control files. sqlplus /nolog CONN / AS SYSDBA SHUTDOWN IMMEDIATE @37_sol_prod1_rebuild_controlfile_1.sql 4) The database is now ready for production use. It is recommended that as a minimum the controlfiles should be backed up. Ideally, it would be a good idea to back up the entire database. # -------------------------------------------------------------------------------- # -------------------------------------------------------------------------------- # ----------------------- Scenario 4 - Loss of Media Archivelog mode = Yes Situation: One or more of the non-SYSTEM database datafiles have been destroyed due to media failure. The restore needs to take place onto alternate media to return the database to production use as soon as possible. # -------------------------------------------------------------------------------- ------------------------- Preferred Recovery Method - using RMAN restore with a recovery catalog ------------------------- 1) Use the OEM Console to log into the OEM repository using an account which has DBA privileges. 2) Select the database which needs recovered from the list of databases at the left side of the OEM Console window. As long as the Oracle Intelligent Agent is running, it should be possible to open the affected database icon in the window, the instance configuration will show that the database is in mounted state. 3) With the database still selected, select the menu item Object/Startup... A dialog box will be displayed when the database fails to startup, listing the file which can't be found. 4) Select the menu item Tools/Database Tools/Backup Management/Recovery... Click the "Next" button. Select "Datafiles". Do not select the "Perform recovery without restoring the datafiles." checkbox. Click the "Next" button. 5) Select the name of the missing file (/u01/prod1/users01.dbf) from the list (its icon may be displayed with a broken right corner to indicate that the database can't open the file). Click the right arrow to move the file from the left side of the window to the right side. Click the "Next" button. 6) Enter a new path for the datafile on the "Rename" window. Use the path /u02/prod1/users01.dbf to move the restored file off the defective media. Make certain that the new pathname is a path that actually exists and that the Oracle database has permission to write into the directory path. Click the "Next" button. 7) Make sure that the same RMAN channel is selected for this restore, as was previously used for performing the RMAN backup. Otherwise, RMAN will not be able to locate the backup files to complete the restore. Click the "Finish" button. Click the "Ok" button to close the Summary window. Click "Ok" to acknowledge that the job has been submitted. Monitor the job completion by clicking on the "Jobs" icon at the left hand side. 8) After the restore/recovery job has completed, click on the database Instance/Configuration icon at the left side of the OEM Console window. Click the "Open" radio button, then click on the "Apply" button near the bottom of the window. Click the "Yes" button on the "Are you sure.." dialog box. Click the "Open" button. 9) The database is now ready for production use. It is now recommended that a full database backup be done, just in case any additional media failures occur. ------------------------- Alternate Recovery Method 1 - using RMAN restore without a recovery catalog ------------------------- 1) Shutdown the database. sqlplus /nolog CONN / AS SYSDBA SHUTDOWN IMMEDIATE EXIT 2) Startup the database. Make note of the errors which are reported in order to determine which tablespaces have been affected. For this example the USERS tablespace is unavailable. The USERS tablespace will need to be restored from a backup to a different mount point. 3) Determine the file# and original path/filename for each file in the database which will need to be restored and recovered. In sqlplus: SELECT FILE#,NAME FROM V$DATAFILE; For this example, datafile 7 has been lost, which is used for the USERS tablespace. The original file path is: /u01/prod1/users01.dbf. 4) Shutdown the database. SQLPLUS /NOLOG CONN / AS SYSDBA ALTER TABLESPACE USERS OFFLINE IMMEDIATE; SHUTDOWN IMMEDIATE EXIT 5) Restore and recover the affected tablespace datafile while renaming it to use a different location to avoid the failed media. rman target / RUN { STARTUP MOUNT; ALLOCATE CHANNEL ch1 TYPE Disk; SET NEWNAME FOR DATAFILE 7 TO '/u02/prod1/users01.dbf'; RESTORE TABLESPACE USERS; SWITCH DATAFILE ALL; #UPDATE THE CONTROL FILE WITH NEW NAME/LOCATION RECOVER TABLESPACE USERS; ALTER DATABASE OPEN; SQL 'ALTER TABLESPACE USERS ONLINE'; RELEASE CHANNEL ch1; } QUIT 6) It is now recommended that a full database backup be done, just in case any additional media failures occur. ------------------------- Alternate Recovery Method 2 - manual restore without using RMAN Note: This method would generally be required if open database backups were performed on the database instead of RMAN backups. ------------------------- 1) Shutdown the database, then startup to the mount state using sqlplus. sqlplus /nolog CONN / AS SYSDBA SHUTDOWN IMMEDIATE STARTUP MOUNT 2) Find the file numbers of the files which need to be recovered by querying V$RECOVER_FILE and V$DATAFILE. SELECT FILE#,ONLINE_STATUS,ERROR FROM V$RECOVER_FILE; In this example, file #7 has been listed in V$RECOVER_FILE as needing recovery. To find out the path/name of file #7, query V$DATAFILE: SELECT FILE#,NAME FROM V$DATAFILE WHERE FILE#=7; The result is: /u01/prod1/users01.dbf 3) Take the lost datafile offline so that the database can be opened: ALTER DATABASE DATAFILE '/u01/prod1/users01.dbf' OFFLINE; ALTER DATABASE OPEN; 4) Now that the database is open, take the tablespace which uses the lost datafile offline. ALTER TABLESPACE USERS OFFLINE IMMEDIATE; 5) Restore the lost datafile from the most recent backup. HOST COPY /backup/prod1_users01.dbf /u02/prod1/users01.dbf 6) Rename the file/location within the database so that the new information is updated in the database control file. ALTER DATABASE RENAME FILE '/u01/prod1/users01.dbf' TO '/u02/prod1/users01.dbf'; 7) Recover the datafile or the entire tablespace, depending upon which is most appropriate. If all of the files which make up a tablespace were affected by the media failure, then issue the command: RECOVER TABLESPACE USERS; Otherwise if only an individual datafile of a tablespace was affected, the single datafile may be recovered independently. Recovering only the affected datafile can reduce the recovery time. RECOVER DATAFILE '/u02/prod1/users01.dbf'; 8) Bring the datafile, then the tablespace online after the file has been recovered: ALTER DATABASE DATAFILE '/u02/prod1/users01.dbf' ONLINE; ALTER TABLESPACE USERS ONLINE; 9) Verify the status of all datafiles to make sure that there aren't additional problems: SELECT FILE#,ONLINE_STATUS,ERROR FROM V$RECOVER_FILE; If the result is "no rows selected" then that means that there are no additional datafiles requiring recovery. SELECT FILE#, STATUS FROM V$DATAFILE; 10) It is now recommended that a full database backup be done, just in case any additional media failures occur. # -------------------------------------------------------------------------------- # ----------------------- Scenario 5 - Loss of an Online Undo Segment Datafile (Open or Closed Database) Archivelog mode = Yes Situation: One or more of the UNDO tablespace datafiles have been destroyed. The restore does not need to take place onto alternate media. # -------------------------------------------------------------------------------- ------------------------- Preferred Recovery Method - using RMAN restore with a recovery catalog ------------------------- 1) Use the OEM Console to log into the OEM repository using an account which has DBA privileges. 2) Select the database which needs recovered from the list of databases at the left side of the OEM Console window. As long as the Oracle Intelligent Agent is running, it should be possible to open the affected database icon in the window, but the instance configuration will show that the database is in mounted state. 3) With the database still selected, select the menu item Object/Startup... A dialog box will be displayed when the database fails to startup, listing the file which can't be found. 4) Select the menu item Tools/Database Tools/Backup Management/Recovery... Click the "Next" button. Select "Datafiles". Do not select the "Perform recovery without restoring the datafiles." checkbox. Click the "Next" button. 5) Select the name of the missing file (/u01/prod1/undo01.dbf) from the list (its icon may be displayed with a broken right corner to indicate that the database can't open the file). Click the right arrow to move the file from the left side of the window to the right side. Click the "Next" button. 6) There is no need to enter a new path for the datafile on the "Rename" window. To skip this feature, just click on the "Next" button. 7) Make sure that the same RMAN channel is selected for this restore, as was previously used for performing the RMAN backup. Otherwise, RMAN will not be able to locate the backup files to complete the restore. Click the "Finish" button. Click the "Ok" button to close the Summary window. Click "Ok" to acknowledge that the job has been submitted. Monitor the job completion by clicking on the "Jobs" icon at the left hand side. 8) After the restore/recovery job has completed, click on the database Instance/Configuration icon at the left side of the OEM Console window. Click the "Open" radio button, then click on the "Apply" button near the bottom of the window. Click the "Yes" button on the "Are you sure.." dialog box. Click the "Open" button. 9) The database is now ready for production use. ------------------------- Alternate Recovery Method 1 - using RMAN restore without a recovery catalog ------------------------- 1) Shutdown the database. sqlplus /nolog CONN / AS SYSDBA SHUTDOWN IMMEDIATE EXIT 2) Startup the database. Make note of the errors which are reported in order to determine which tablespaces have been affected. For this example the undotbs tablespace is unavailable. The undotbs tablespace datafile will need to be restored from a backup, but since this failure is not due to a media failure the datafile may be restored to the same location as the original datafile. 3) Determine the file# and original path/filename for each file in the database which will need to be restored and recovered. In sqlplus: SELECT FILE#,NAME FROM V$DATAFILE; For this example, datafile 2 has been lost, which is used for the undotbs tablespace. The file path is: /u01/prod1/undo01.dbf. 4) Shutdown the database. sqlplus /nolog CONN / AS SYSDBA SHUTDOWN IMMEDIATE EXIT 5) Restore and recover the undotbs tablespace. rman target / RUN { STARTUP MOUNT; ALLOCATE CHANNEL ch1 TYPE Disk; RESTORE datafile '/u01/prod1/undo01.dbf'; RECOVER datafile '/u01/prod1/undo01.dbf'; ALTER DATABASE OPEN; RELEASE CHANNEL ch1; } QUIT 6) The database is now available for production use. ------------------------- Alternate Recovery Method 2 - manual restore without using RMAN Note: This method would generally be required if open database backups had been performed on the database instead of RMAN backups. ------------------------- 1) Shutdown the database, then startup to the mount state using sqlplus. sqlplus /nolog CONN / AS SYSDBA SHUTDOWN IMMEDIATE STARTUP MOUNT 2) Find the file numbers of the files which need to be recovered by querying V$RECOVER_FILE and V$DATAFILE. SELECT FILE#,ONLINE_STATUS,ERROR FROM V$RECOVER_FILE; In this example, file #2 has been listed in V$RECOVER_FILE as a file which needs recovery. To find out the path/name of file #2, query V$DATAFILE: SELECT FILE#,NAME FROM V$DATAFILE WHERE FILE#=2; The result is: /u01/prod1/undo01.dbf 3) Take the lost datafile offline so that the database can be opened: ALTER DATABASE DATAFILE '/u01/prod1/undo01.dbf' OFFLINE; 4) Restore the lost datafile from the most recent backup. host COPY /backup/prod1_undo01.dbf /u01/prod1/undo01.dbf 5) Recover the datafile or the entire tablespace, depending upon which is most appropriate. If all of the files which make up a tablespace were affected by the media failure, then issue the command: RECOVER TABLESPACE undotbs; Press return if prompted for the logfile to use during recovery. Otherwise if only an individual datafile of a tablespace was affected, the single datafile may be recovered independently. Recovering only the affected datafile can reduce the recovery time. RECOVER DATAFILE '/u01/prod1/undo01.dbf'; 6) Bring the datafile, then the tablespace online after the file has been recovered: ALTER DATABASE DATAFILE '/u01/prod1/undo01.dbf' ONLINE; 7) Open the database. ALTER DATABASE OPEN; 8) Bring the undotbs tablespace online. ALTER TABLESPACE undotbs ONLINE; 9) Verify the status of all datafiles to make sure that there aren't additional problems: SELECT FILE#,ONLINE_STATUS,ERROR FROM V$RECOVER_FILE; If the result is "no rows selected" then that means that there are no additional datafiles requiring recovery. Verify that there aren't any additional database files which are offline. SELECT FILE#, STATUS FROM V$DATAFILE; 10) The database is now available for production use. # -------------------------------------------------------------------------------- # ----------------------- Scenario 6 - Loss of a SYSTEM tablespace datafile Archivelog mode = Yes Situation: One or more of the SYSTEM tablespace datafiles have been destroyed. The restore does not need to take place onto alternate media. # -------------------------------------------------------------------------------- ------------------------- Preferred Recovery Method - using RMAN restore with a recovery catalog ------------------------- 1) Use the OEM Console to log into the OEM repository using an account which has DBA privileges. 2) Select the database which needs recovered from the list of databases at the left side of the OEM Console window. As long as the Oracle Intelligent Agent is running, it should be possible to open the affected database icon in the window, the instance configuration will show that the database is in the mounted state. 3) With the database still selected, select the menu item Object/Startup... A dialog box will be displayed when the database fails to startup, listing the file which can't be found. 4) Select the menu item Tools/Database Tools/Backup Management/Recovery... Click the "Next" button. Select "Datafiles". Do not select the "Perform recovery without restoring the datafiles." checkbox. Click the "Next" button. 5) Select the name of the missing file (/u01/prod1/system01.dbf) from the list (its icon may be displayed with a broken right corner to indicate that the database can't open the file). Click the right arrow to move the file from the left side of the window to the right side. Click the "Next" button. 6) There is no need to enter a new path for the datafile on the "Rename" window. To skip this feature, just click on the "Next" button. 7) Make sure that the same RMAN channel was selected for this restore, as was previously used for performing the RMAN backup. Otherwise, RMAN will not be able to locate the backup files to complete the restore. Click the "Finish" button. Click the "Ok" button to close the Summary window. Click "Ok" to acknowledge that the job has been submitted. Monitor the job completion by clicking on the "Jobs" icon at the left hand side. 8) After the restore/recovery job has completed, click on the database Instance/Configuration icon at the left side of the OEM Console window. Click the "Open" radio button, then click on the "Apply" button near the bottom of the window. Click the "Yes" button on the "Are you sure.." dialog box. Click the "Open" button. 9) The database is now ready for production use. ------------------------- Alternate Recovery Method 1 - using RMAN restore without a recovery catalog ------------------------- 1) Shutdown the database. sqlplus /nolog CONN / AS SYSDBA SHUTDOWN IMMEDIATE EXIT 2) Startup the database. Make note of the errors which are reported in order to determine which tablespaces have been affected. For this example the SYSTEM tablespace is unavailable. The SYSTEM tablespace datafile will need to be restored from a backup, but since this failure is not due to a media failure the datafile may be restored to the same location as the original datafile. 3) Determine the file# and original path/filename for each file in the database which will need to be restored and recovered. In sqlplus: SELECT FILE#,NAME FROM V$DATAFILE; For this example, datafile 1 has been lost, which is used for the SYSTEM tablespace. The file path is: /u01/prod1/system01.dbf. 4) Shutdown the database. sqlplus /nolog CONN / AS SYSDBA SHUTDOWN IMMEDIATE EXIT 5) Restore and recover the SYSTEM tablespace datafile. rman target / RUN { STARTUP MOUNT; ALLOCATE CHANNEL ch1 TYPE Disk; RESTORE datafile '/u01/prod1/system01.dbf'; RECOVER datafile '/u01/prod1/system01.dbf'; ALTER DATABASE OPEN; RELEASE CHANNEL ch1; } QUIT 6) The database is now available for production use. ------------------------- Alternate Recovery Method 2 - manual restore without using RMAN Note: This method would generally be required if open database backups were being performed on the database instead of RMAN backups. ------------------------- 1) Shutdown the database, then startup to the mount state using sqlplus. sqlplus /nolog CONN / AS SYSDBA SHUTDOWN IMMEDIATE STARTUP MOUNT 2) Find the file numbers of the files which need to be recovered by querying V$RECOVER_FILE and V$DATAFILE. SELECT FILE#,ONLINE_STATUS,ERROR FROM V$RECOVER_FILE; In this scenario, file #1 has been listed in V$RECOVER_FILE as needing recovery. To find out the path/name of file #1, query V$DATAFILE: SELECT FILE#,NAME FROM V$DATAFILE WHERE FILE#=1; The result is: /u01/prod1/system01.dbf 3) Take the lost datafile offline so that the database can be opened: ALTER DATABASE DATAFILE '/u01/prod1/system01.dbf' OFFLINE; 4) Restore the lost datafile from the most recent backup. host copy /backup/prod1_/u01/prod1/system01.dbf /u01/prod1/system01.dbf 5) Recover the datafile or the entire tablespace, depending upon which is most appropriate. If all of the files which make up a tablespace were affected by the media failure, then issue the command: RECOVER TABLESPACE SYSTEM; Press return if prompted for the logfile to use during recovery. Continue pressing the return key when prompted until the recovery process displays the message "Media recovery complete." Otherwise if only an individual datafile of a tablespace was affected, the single datafile may be recovered independently. Recovering only the affected datafile can reduce the recovery time. RECOVER DATAFILE '/u01/prod1/system01.dbf'; 6) Bring the datafile, then the tablespace online after the file has been recovered: ALTER DATABASE DATAFILE '/u01/prod1/system01.dbf' ONLINE; 7) Open the database. ALTER DATABASE OPEN; 8) Bring the SYSTEM tablespace online. ALTER TABLESPACE SYSTEM ONLINE; 9) Verify the status of all datafiles to make sure that there aren't additional problems: SELECT FILE#,ONLINE_STATUS,ERROR FROM V$RECOVER_FILE; If the result is "no rows selected" then that means that there are no additional datafiles requiring recovery. Verify that there aren't any additional database files which are offline. SELECT FILE#, STATUS FROM V$DATAFILE; 10) The database is now available for production use. # -------------------------------------------------------------------------------- # ----------------------- Scenario 7 - Loss of a Non-System, Non-Rollback Segment Datafile Archivelog mode = Yes Situation: One or more datafiles from a non-SYSTEM tablespace have been destroyed. The restore does not need to take place onto alternate media. # -------------------------------------------------------------------------------- ------------------------- Preferred Recovery Method - using RMAN restore with a recovery catalog ------------------------- 1) Use the OEM Console to log into the OEM repository using an account which has DBA privileges. 2) Select the database which needs recovered from the list of databases at the left side of the OEM Console window. As long as the Oracle Intelligent Agent is running, it should be possible to open the affected database icon in the window, the instance configuration will show that the database is in mounted state. 3) With the database still selected, select the menu item Object/Startup... A dialog box will be displayed when the database fails to startup, listing the file which can't be found. 4) Select the menu item Tools/Database Tools/Backup Management/Recovery... Click the "Next" button. Select "Datafiles". Do not select the "Perform recovery without restoring the datafiles." checkbox. Click the "Next" button. 5) Select the name of the missing file (/u01/prod1/users01.dbf) from the list (its icon may be displayed with a broken right corner to indicate that the database can't open the file). Click the right arrow to move the file from the left side of the window to the right side. Click the "Next" button. 6) There is no need to enter a new path for the datafile on the "Rename" window. To skip this feature, just click on the "Next" button. 7) Make sure that the same RMAN channel was selected for this restore, as was previously used for performing the RMAN backup. Otherwise, RMAN will not be able to locate the backup files to complete the restore. Click the "Finish" button. Click the "Ok" button to close the Summary window. Click "Ok" to acknowledge that the job has been submitted. Monitor the job completion by clicking on the "Jobs" icon at the left hand side. 8) After the restore/recovery job has completed, click on the database Instance/Configuration icon at the left side of the OEM Console window. Click the "Open" radio button, then click on the "Apply" button near the bottom of the window. Click the "Yes" button on the "Are you sure.." dialog box. Click the "Open" button. 9) The database is now ready for production use. ------------------------- Alternate Recovery Method 1 - using RMAN restore without a recovery catalog ------------------------- 1) Shutdown the database. sqlplus /nolog CONN / AS SYSDBA SHUTDOWN IMMEDIATE EXIT 2) Startup the database. Make note of the errors which are reported in order to determine which tablespaces have been affected. For this example the USERS tablespace is unavailable. The USERS tablespace datafile will need to be restored from a backup, but since this failure is not due to a media failure the datafile may be restored to the same location as the original datafile. 3) Determine the file# and original path/filename for each file in the database which will need to be restored and recovered. In sqlplus: SELECT FILE#,NAME FROM V$DATAFILE where FILE#=4; For this example, datafile 4 has been lost, which is used for the USERS tablespace. The file path is: /u01/prod1/users01.dbf. 4) Shutdown the database. sqlplus /nolog CONN / AS SYSDBA SHUTDOWN IMMEDIATE EXIT 5) Restore and recover the USERS tablespace datafile. rman target / RUN { STARTUP MOUNT; ALLOCATE CHANNEL ch1 TYPE Disk; RESTORE DATAFILE '/u01/prod1/users01.dbf'; RECOVER DATAFILE '/u01/prod1/users01.dbf'; ALTER DATABASE OPEN; RELEASE CHANNEL ch1; } QUIT 6) Verify the status of all datafiles to make sure that there aren't additional problems: SELECT FILE#,ONLINE_STATUS,ERROR FROM V$RECOVER_FILE; If the result is "no rows selected" then that means that there are no additional datafiles requiring recovery. Verify that there aren't any additional database files which are offline. SELECT FILE#, STATUS FROM V$DATAFILE; 7) The database is now available for production use. ------------------------- Alternate Recovery Method 2 - manual restore without using RMAN Note: This method would generally be required if open database backups are being performed on the database instead of RMAN backups. ------------------------- 1) Shutdown the database, then startup to the mount state using sqlplus. sqlplus /nolog conn / as sysdba SHUTDOWN IMMEDIATE STARTUP MOUNT 2) Find the file numbers of the files which need to be recovered by querying V$RECOVER_FILE and V$DATAFILE. SELECT FILE#,ONLINE_STATUS,ERROR FROM V$RECOVER_FILE; In this scenario, file #4 has been listed in V$RECOVER_FILE as needing recovery. To find out the path/name of file #4, query V$DATAFILE: SELECT FILE#,NAME FROM V$DATAFILE WHERE FILE#=4; The result is: users01.dbf 3) Take the lost datafile offline so that the database can be opened: ALTER DATABASE DATAFILE '/u01/prod1/users01.dbf' OFFLINE; ALTER DATABASE OPEN; 4) Take the USERS tablespace offline because the datafile has not yet been restored. ALTER TABLESPACE USERS OFFLINE IMMEDIATE; 5) Restore the lost datafile from the most recent backup. host COPY /backup/prod1_users01.dbf /u01/prod1/users01.dbf 6) Recover the datafile or the entire tablespace, depending upon which is most appropriate. If all of the files which make up a tablespace were affected by the media failure, then issue the command: RECOVER TABLESPACE USERS; Press return if prompted for the logfile to use during recovery. Continue pressing the return key when prompted until the recovery process displays the message "Media recovery complete." Otherwise if only an individual datafile of a tablespace was affected, the single datafile may be recovered independently. Recovering only the affected datafile can reduce the recovery time. RECOVER DATAFILE '/u01/prod1/users01.dbf'; 7) Bring the datafile, then the tablespace online after the file has been recovered: ALTER DATABASE DATAFILE '/u01/prod1/users01.dbf' ONLINE; 8) Bring the USERS tablespace online. ALTER TABLESPACE USERS ONLINE; 9) Verify the status of all datafiles to make sure that there aren't additional problems: SELECT FILE#,ONLINE_STATUS,ERROR FROM V$RECOVER_FILE; If the result is "no rows selected" then that means that there are no additional datafiles requiring recovery. Verify that there aren't any additional database files which are offline. SELECT FILE#, STATUS FROM V$DATAFILE; 10) The database is now available for production use. # -------------------------------------------------------------------------------- # ----------------------- Scenario 8 - Recover from User Errors - dropped table or deleted data Archivelog mode = Yes Situation: A table has been truncated or dropped from the database. # -------------------------------------------------------------------------------- ------------------------- Alternate Recovery Method 1 - using RMAN with a recovery catalog ------------------------- 1) Verify the time when the error occurred. Neither a truncate nor a dropped table event will show up in the /u01/bdump/prod1ALRT.LOG file. If there is any uncertainty concerning the time of the event, it might be necessary to use Log Miner to determine when the last successful write occurred to the affected table. Note: The graphical interface to Log Miner included with Oracle 9i requires a connection to an OEM server. 2) For the purposes of this example, we know that a table was dropped at 3:23PM, and that the last data was written to the table at 3:19PM. Due to the importance of this table in the database, most other database transactions started failing after this table was dropped due to referential integrity failures. Therefore a point in time database recovery will be performed. 3) Use the OEM Console to log into the OEM repository using an account which has DBA privileges. 4) Select the database which needs recovered from the list of databases at the left side of the OEM Console window. 5) Shutdown the database via the OEM Console then start up the database to the Mount state. Click on the Instance/Configuration icon at the left hand window. Click on the "Show all states" checkbox. Click on the "Shutdown" radio button. Click on the "Apply" button at the bottom of the window. Click on the "Immediate" radio button in the window which pops up. Click the "Close" button in the progress dialog box after the database has been shutdown. Close, then re-open the Instance/Configuration icon. Click on the "Mounted" radio button. Click on the "Apply" button at the bottom of the window. Click "Ok" on the dialog window which pops up. Click the "Close" button in the progress dialog box after the database has been mounted. 6) With the database still selected, select the menu item Object/Startup... A dialog box will be displayed when the database fails to startup, listing the file which can't be found. 7) Select the menu item Tools/Database Tools/Backup Management/Recovery... Click the "Next" button. Select "Database". Do not select the "Perform recovery without restoring the datafiles." checkbox. Click the "Next" button. 8) Click the "Until" checkbox. Enter the correct date/time for the recovery operation. For this example, the recovery time should be set to 3:20PM. Click the "Next" button. 9) There is no need to enter a new path for the datafiles on the "Rename" window. To skip this feature, just click on the "Next" button. 10) Make sure that the same RMAN channel was selected for this restore, as was previously used for performing the RMAN backup. Otherwise, RMAN will not be able to locate the backup files to complete the restore. Click the "Finish" button. Click the "Ok" button to close the Summary window. Click "Ok" to acknowledge that the job has been submitted. Monitor the job completion by clicking on the "Jobs" icon at the left hand side. 11) After the restore/recovery job has completed, click on the database Instance/Configuration icon at the left side of the OEM Console window. Click the "Open" radio button, then click on the "Apply" button near the bottom of the window. Click the "Resetlogs" button on the "Open Database" dialog box. Click the "Open" button. Click the "Ok" button in the small "Oracle Enterprise Manager" progress window which appears after the database has been opened. 12) Prior to performing the next RMAN backup, it will be necessary to reset the database information in the RMAN catalog. If necessary, re-select the icon for the database which has just been recovered. Select the menu item Tools/Database Tools/Backup Management/Maintenance... Click the "Next" button. Click the "Reset database" radio button. Click the "Next" button. Click the "Finish" button. Click the "Ok" button on Summary window. Click "Ok" to acknowledge that the job has been submitted. 13) Perform another full RMAN database backup since the previous backups are now unusable due to the database now having been set to a different database incarnation number. 14) Perform any additional manual recovery tasks which need to be performed to make the database operational, including manually re-importing or re-entering lost data. 15) The database is now ready for production use. ------------------------- Alternate Recovery Method 2 - using RMAN without a recovery catalog ------------------------- 1) Verify the time when the error occurred. Neither a truncate nor a dropped table event will show up in the /u01/bdump/prod1ALRT.LOG file. If there is any uncertainty concerning the time of the event, it might be necessary to use Log Miner to determine when the last successful write occurred to the affected table. Note: The graphical interface to Log Miner included with Oracle 9i requires a connection to an OEM server. 2) For the purposes of this example, we know that a table was dropped at 3:23PM, and that the last data was written to the table at 3:19PM. Due to the importance of this table in the database, most other database transactions started failing after this table was dropped due to referential integrity failures. Therefore a point in time database recovery will be performed. 3) Shutdown the database. sqlplus /nolog CONN / AS SYSDBA SHUTDOWN IMMEDIATE 4) Based upon the above information, a point in time database recovery will be performed to a time of 3:20PM Use RMAN to perform point in time database recovery. Because an incomplete recovery is being performed on the database, it will be opened with resetlogs. rman target / STARTUP MOUNT RUN { ALLOCATE CHANNEL ch1 TYPE Disk; SET UNTIL TIME = "to_date('2002/02/12 15:20:00','YYYY/MM/DD HH24:MI:SS')"; RESTORE DATABASE; RECOVER DATABASE; ALTER DATABASE OPEN RESETLOGS; } QUIT 5) At this time, RMAN will have rolled back the tablespace data to 3:20PM and re-opened the database. 6) Since the database was opened with resetlogs, it is necessary to perform another full backup of the database because all previous backups will be invalid. rman target / RUN { ALLOCATE CHANNEL ch1 TYPE Disk MAXPIECESIZE = 1900M; BACKUP FULL DATABASE INCLUDE CURRENT CONTROLFILE FORMAT '/backup/%d_datafile_%s_%p.bak' TAG 'prod1_datafile_daily'; } RUN { ALLOCATE CHANNEL ch1 TYPE Disk MAXPIECESIZE = 1900M; BACKUP ARCHIVELOG ALL DELETE ALL INPUT FORMAT '/archive/%d_archivelog_%s_%p.bak ' TAG 'prod1_archivelog_daily '; } QUIT Note: If the point in time recovery is attempted, and the recovery point in time is specified incorrectly, it is not just a simple matter of running the RMAN restore/recovery command again with another time specification. It will be necessary to change the database incarnation to its previous value before attempting the RMAN restore/recovery again. The database incarnation value must match the database incarnation value which was being used within the database at the time the RMAN backup was performed. This procedure can only be done when using RMAN with a recovery catalog instead of using the controlfile as the RMAN catalog. 7) The database is now available for production use. ------------------------- Alternate Recovery Method 3 - using export file ------------------------- 1) If the amount of data affected by the error is minimal, then re-import the affected data from the most recent full database export file. This recovery method is the preferred method if the table is a static table which has not changed since the last full database export. This way no transactions need to be lost because a rollback of the database does not occur. ------------------------- Alternate Recovery Method 4 - using RMAN point in time recovery and export file ------------------------- If the elapsed time interval has been short or the number of transactions entered into the database has been 0 or very small, use RMAN to perform a point in time tablespace recovery. 1) Verify the time when the error occurred. This time needs to coincide with the clock on the server, not the user's client computer. 2) Rename the existing nightly export file in the /backup directory. mv /backup/prod1_full_daily.dmp /backup/prod1_prev_full_daily.dmp 3) Perform a full database export, to preserve transactions which have occurred since the error occurred. The shell script /opt/server_scripts/prod1_export_full_db_1.sh can be used to perform this export. 4) Perform a point in time tablespace recovery with RMAN to a point in time just before the error occurred. All transactions after the recovery time specified for the recovery will be lost. 5) Import the data from the most recent export file into another database. 6) Manually move the data for recent transactions from the testing database to the original database. ------------------------- Alternate Recovery Method 5 - manual data entry ------------------------- If the amount of lost data is minimal, and the table has not been dropped or truncated it may be better to manually re-enter the data which has been lost. This recovery method may also be considered in situations in which the number of transactions which would be lost during a database point in time recovery is high. # -------------------------------------------------------------------------------- # ----------------------- Scenario 9 - Failure during Open Database Backup Note: This backup method was previously known as the "Hot Backup" method prior to Oracle 9i. # -------------------------------------------------------------------------------- ------------------------- Preferred Recovery Method - use RMAN instead of Open Database Backups ------------------------- 1) Recommendation - Prevent the possibility of a tablespace being left in backup mode by not performing Open Database Backups. Use RMAN to perform backups instead of Open database backups. RMAN backups put less of a load onto the database during the backup process. RMAN backups allow for a faster database recovery process because RMAN tracks all of the components of the backup process to automate the recovery process. ------------------------- Alternate Recovery Method - using Open database backups ------------------------- 1) This scenario uses the USERS tablespace as an example of a tablespace which has been left in backup mode due to a database failure during the online backup process. The file associated with this tablespace is: /u01/prod1/users01.dbf. 2) When the database was started up after the failure, the following errors were displayed: ORA-01113: file 7 needs media recovery ORA-01110: data file 7: '/u01/prod1/users01.dbf' 3) Query the V$BACKUP view to determine which files were in backup mode during the failure. SELECT * FROM V$BACKUP; The results of the query show that file #7 is still in backup mode (because the file status is "ACTIVE"). 4) The quickest way to resolve this problem is to issue the following command via sqlplus to take all tablespaces and datafiles out of backup mode: ALTER DATABASE END BACKUP; Alternately you may issue commands to take just the tablespace or datafile out of backup mode: ALTER TABLESPACE USERS END BACKUP; ALTER DATABASE DATAFILE '/u01/prod1/users01.dbf' END BACKUP; 5) Open the database. ALTER DATABASE OPEN; 6) If any additional files or tablespaces were in backup mode, errors would have been displayed when the database was opened. But it is still a good idea to verify that there aren't any tablespaces offline for any other reason: SELECT FILE#,STATUS FROM V$DATAFILE; SELECT * FROM V$RECOVER_FILE; Querying V$BACKUP view should show that the status of all files is "NOT ACTIVE", which means that no datafiles are currently in backup mode. SELECT * FROM V$BACKUP; 7) Re-start the backup process with the /opt/server_scripts/prod1_open_db_backup_job_1.sh script so that a complete backup is completed for the database. # -------------------------------------------------------------------------------- # ----------------------- Scenario 10 - Missing Data File - non-SYSTEM tablespace file Archivelog mode = Yes Situation: A datafile has been deleted via OS commands while the database was down. The datafile and its associated tablespace were intended to be deleted therefore the goal is to simply get the database started up quickly. # -------------------------------------------------------------------------------- ------------------------- Preferred Recovery Method - drop datafile and tablespace ------------------------- 1) For this example the datafile for the USERS3 tablespace was intentionally deleted when the database was down, therefore the following error message text was displayed when the DBA started up the database: ORA-01157: cannot identify/lock data file 8 - see DBWR trace file ORA-01110: data file 8: '/u01/prod1/users3_01.dbf' 2) Since this was an intentional deletion of a datafile, no recovery will be performed. The datafile will first be taken offline with the DROP option: ALTER DATABASE DATAFILE '/u01/prod1/users3_01.dbf' OFFLINE DROP; 3) The database will now be opened so that users may access it: ALTER DATABASE OPEN; 4) However, it is also necessary to remove the tablespace associated with the datafile from the data dictionary to prevent errors from occurring during attempted access to objects within the affected tablespace. DROP TABLESPACE USERS3 INCLUDING CONTENTS CASCADE CONSTRAINTS; 5) The database is now available for production use. # -------------------------------------------------------------------------------- # ----------------------- Scenario 11 - Loss of a Datafile and Missing Archive Log File Archivelog mode = Yes Situation: A datafile and one archivelog file have been deleted. A backup is available to restore the datafile, but there is no other copy of the missing archivelog file. # -------------------------------------------------------------------------------- ------------------------- Preferred Recovery Method - using RMAN with a recovery catalog ------------------------- 1) Use the OEM Console to log into the OEM repository using an account which has DBA privileges. 2) Select the database which needs recovered from the list of databases at the left side of the OEM Console window. 3) Shutdown the database via the OEM Console then start up the database to the Mount state. Click on the Instance/Configuration icon at the left hand window. Click on the "Show all states" checkbox. Click on the "Shutdown" radio button. Click on the "Apply" button at the bottom of the window. Click on the "Immediate" radio button in the window which pops up. Click the "Close" button in the progress dialog box after the database has been shutdown. Close, then re-open the Instance/Configuration icon. Click on the "Mounted" radio button. Click on the "Apply" button at the bottom of the window. Click "Ok" on the dialog window which pops up. Click the "Close" button in the progress dialog box after the database has been mounted. 4) Select the menu item Tools/Database Tools/Backup Management/Recovery... Click the "Next" button. Select "Entire Database". Do not select the "Perform recovery without restoring the datafiles." checkbox. Click the "Next" button. 5) Do not click the "Until" checkbox in the "Restore Until" window. Click the "Next" button. 6) There is no need to enter a new path for the datafiles on the "Rename" window. To skip this feature, just click on the "Next" button. 7) Make sure that the same RMAN channel was selected for this restore, as was previously used for performing the RMAN backup. Otherwise, RMAN will not be able to locate the backup files to complete the restore. Click the "Finish" button. Click the "Ok" button to close the Summary window. Click "Ok" to acknowledge that the job has been submitted. Monitor the job completion by clicking on the "Jobs" icon at the left hand side. 8) After the restore/recovery job has completed, check the completion status of the job under the "History" tab of the OEM Jobs window. The database files will have been restored but the recovery will have failed due to the missing log file. 9) Use sqlplus to start recovery again, with the cancel option. Enter cancel when prompted for the missing archivelog file: RECOVER DATABASE UNTIL CANCEL; CANCEL; 10) Open the database with the resetlogs option because an incomplete recovery has been done. ALTER DATABASE OPEN RESETLOGS; 11) The database is now ready for production use. However another RMAN full database backup should be performed immediately. 12) Prior to performing the next RMAN backup, it will be necessary to reset the database information in the RMAN catalog. If necessary, re-select the icon for the database which has just been recovered. Select the menu item Tools/Database Tools/Backup Management/Maintenance... Click the "Next" button. Click the "Reset database" radio button. Click the "Next" button. Click the "Finish" button. Click the "Ok" button on Summary window. Click "Ok" to acknowledge that the job has been submitted. 13) Perform another full RMAN database backup since the previous backups are now unusable due to the database now having been set to a different database incarnation number. 14) Perform any additional manual recovery tasks which need to be performed to make the database operational, including manually re-importing or re-entering lost data. ------------------------- Alternate Recovery Method 1 - using RMAN restore without a recovery catalog ------------------------- 1) Shutdown the database. sqlplus /nolog CONN / AS SYSDBA SHUTDOWN IMMEDIATE EXIT 2) Startup the database. Make note of the errors which are reported in order to determine which tablespaces have been affected. For this example the USERS3 tablespace datafile is unavailable. ORA-01157: cannot identify/lock data file 8 - see DBWR trace file ORA-01110: data file 8: '/u01/prod1/users3_01.dbf' The USERS3 tablespace datafile will need to be restored from a backup, since this failure is not due to a media failure the datafile may be restored to the same location as the original datafile. 3) If it is known in advance that there are one or more missing archivelog files, then a determination should be made concerning the amount and importance of the data which will be lost. If necessary, take the affected datafile offline, start up the database and perform a full database export. Then proceed with the restore/recovery process listed below in parallel with importing the data into a test database server. The data imported into the test server can be used to manually recover data from other tablespaces after the point in time where the last archivelog file was applied to the production database. 4) Determine the file# and original path/filename for each file in the database which will need to be restored and recovered. In sqlplus: SELECT FILE#,NAME FROM V$DATAFILE WHERE FILE#=8; For this example, datafile 8 has been lost, which is used for the USERS3 tablespace. The file path is: /u01/prod1/users3_01.dbf. 5) Shutdown the database. sqlplus /nolog CONN / AS SYSDBA SHUTDOWN IMMEDIATE EXIT 6) Restore and recover the database. rman target / RUN { STARTUP MOUNT; ALLOCATE CHANNEL ch1 TYPE Disk; RESTORE DATABASE; RECOVER DATABASE; ALTER DATABASE OPEN RESETLOGS; } QUIT RMAN reports the following error due to the missing archivelog file: RMAN-06053: unable to perform media recovery because of missing log RMAN-06025: no backup of log thread 1 seq 16 scn 629211 found to restore RMAN-06025: no backup of log thread 1 seq 15 scn 629209 found to restore RMAN-06025: no backup of log thread 1 seq 14 scn 629132 found to restore 7) Issue the following command within sqlplus to manually recover the database and cancel the recovery prior to the missing archivelog file: RECOVER DATABASE UNTIL CANCEL; 8) Since an incomplete database recovery was performed, the database must be opened with the resetlogs option. ALTER DATABASE OPEN RESETLOGS; 9) Verify the status of all datafiles to make sure that there aren't additional problems: SELECT FILE#,ONLINE_STATUS,ERROR FROM V$RECOVER_FILE; If the result is "no rows selected" then that means that there are no additional datafiles requiring recovery. Verify that there aren't any additional database files which are offline. SELECT FILE#, STATUS FROM V$DATAFILE; 10) Since the database was opened with resetlogs, it is necessary to perform another full backup of the database because all previous backups will be invalid. rman target / RUN { ALLOCATE CHANNEL ch1 TYPE Disk MAXPIECESIZE = 1900M; BACKUP FULL DATABASE INCLUDE CURRENT CONTROLFILE FORMAT '/backup/%d_datafile_%s_%p.bak' TAG 'prod1_datafile_daily '; } RUN { ALLOCATE CHANNEL ch1 TYPE Disk MAXPIECESIZE = 1900M; BACKUP ARCHIVELOG ALL DELETE ALL INPUT FORMAT '/archive/%d_archivelog_%s_%p.bak' TAG 'prod1_archivelog_daily'; } QUIT 11) The database is now available for production use. 12) Now that the database is available for production use, the process of recovering the data from the test database may proceed. # -------------------------------------------------------------------------------- # ----------------------- Scenario 12 - Loss of Non-Essential Datafile When Database is Down Archivelog mode = Yes Situation: This scenario assumes that the datafile for the INDX tablespace has been deleted. # -------------------------------------------------------------------------------- ------------------------- Preferred Recovery Method - take datafile offline - rebuild indexes after database is online ------------------------- For the purposes of this scenario the INDX tablespace is considered non-essential because it can often be taken offline in order to bring up the database quickly. The indexes will then be rebuilt manually via scripts run by the DBA. The rebuilding of these indexes can only be performed manually if the DBA maintains the SQL scripts required to rebuild them. If the SQL to rebuild the indexes is not available, then the INDX datafile should be restored and recovered as with any other datafile. This determination will need to be made on a case by case basis. 1) Startup the database. When the database starts up, the following errors will be shown: ORA-01157: cannot identify/lock data file 6 - see DBWR trace file ORA-01110: data file 6: '/u01/prod1/indx01.dbf' These errors show that file#6 is unavailable. 2) Shutdown the database, then startup to the mount state using sqlplus. sqlplus /nolog CONN / AS SYSDBA SHUTDOWN IMMEDIATE STARTUP MOUNT 3) Find the file numbers of the files which need to be recovered by querying V$RECOVER_FILE and V$DATAFILE. SELECT FILE#,ONLINE_STATUS,ERROR FROM V$RECOVER_FILE; In this example, file #6 has been listed in V$RECOVER_FILE as needing recovery. To find out the path/name of file #6, query V$DATAFILE: SELECT FILE#,NAME FROM V$DATAFILE WHERE FILE#=6; The result is: /u01/prod1/indx01.dbf 4) Take the lost datafile offline so that the database can be opened: ALTER DATABASE DATAFILE '/u01/prod1/indx01.dbf' OFFLINE; ALTER DATABASE OPEN; 5) The database is now available for production use. However any queries which rely on indexes will be expected to take longer until the indexes are rebuilt. 6) Drop the INDX tablespace. Note: Dropping of the INDX tablespace should not be done if there are any objects located within the INDX tablespace which won't be re-built by using the DBA's index rebuild scripts. DROP TABLESPACE INDX INCLUDING CONTENTS CASCADE CONSTRAINTS; 7) Re-create the INDX tablespace using the same datafile filename using the reuse option. CREATE TABLESPACE INDX DATAFILE '/u01/prod1/indx01.dbf' SIZE 50M REUSE AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL AUTOALLOCATE LOGGING ONLINE; 8) Re-build all of the indexes with the DBA's index rebuild scripts. # -------------------------------------------------------------------------------- # ----------------------- Scenario 13 - Recover a Lost Datafile with No Backup Archivelog mode = Yes Situation: The USERS3 tablespace contains 2 datafiles. The database has been backed up, but the 2nd datafile was added after the most recent database backup. Therefore there is no backup of the 2nd datafile. # -------------------------------------------------------------------------------- ------------------------- Preferred Recovery Method - using RMAN restore with a recovery catalog ------------------------- 1) In the OEM Console application, click on the database Instance/Configuration icon at the left side of the OEM Console window. Click the "Open" radio button, then click on the "Apply" button near the bottom of the window. The OEM Console application will attempt to open the database, but will fail with an error due to the missing datafile. Click the "Ok" button after noting the tablespace datafile number, file and pathname. Click the "Close" button in the "Starting up database..." progress window. 2) Shutdown the database via the OEM Console then start up the database to the Mount state. Click on the Instance/Configuration icon at the left hand window. Click on the "Show all states" checkbox. Click on the "Shutdown" radio button. Click on the "Apply" button at the bottom of the window. Click on the "Immediate" radio button in the window which pops up. Click the "Close" button in the progress dialog box after the database has been shutdown. Close, then re-open the Instance/Configuration icon. Click on the "Mounted" radio button. Click on the "Apply" button at the bottom of the window. Click "Ok" on the dialog window which pops up. Click the "Close" button in the progress dialog box after the database has been mounted. 3) Use sqlplus to re-create the missing datafile prior to performing the RMAN restore. sqlplus /nolog CONN / AS SYSDBA STARTUP MOUNT; ALTER DATABASE DATAFILE '/u01/prod1/users3_02.dbf' OFFLINE; ALTER DATABASE CREATE DATAFILE '/u01/prod1/users3_02.dbf'; ALTER DATABASE DATAFILE '/u01/prod1/users3_02.dbf' ONLINE; 4) Use the OEM Console to log into the OEM repository using an account which has DBA privileges. 5) Select the database which needs recovered from the list of databases at the left side of the OEM Console window. 6) Select the menu item Tools/Database Tools/Backup Management/Recovery... Click the "Next" button. Select "Tablespaces". You MUST select the "Perform recovery without restoring the datafiles" checkbox because the missing datafile is not available within any previous RMAN backup. Click the "Next" button. 7) Select the name of the tablespace which requires the missing datafile from the list. Click the right arrow to move the tablespace name from the left side of the window to the right side. Click the "Next" button. 8) There is no need to enter a new path for the datafile on the "Rename" window. To skip this feature, just click on the "Next" button. 9) Make sure that the same RMAN channel was selected for this restore, as was previously used for performing the RMAN backup. Otherwise, RMAN will not be able to locate the backup files to complete the restore. Click the "Finish" button. Click the "Ok" button to close the Summary window. Click "Ok" to acknowledge that the job has been submitted. Monitor the job completion by clicking on the "Jobs" icon in the left side of the OEM window. 10) After the restore/recovery job has completed, click on the database Instance/Configuration icon at the left side of the OEM Console window. Click the "Open" radio button, then click on the "Apply" button near the bottom of the window. Click the "Yes" button on the "Are you sure.." dialog box. Click the "Open" button. 9) The database is now ready for production use. ------------------------- Alternate Recovery Method 1 - using RMAN restore - without a recovery catalog ------------------------- 1) Startup the database. Make note of the errors which are reported in order to determine which datafile has been affected. For this example one of the USERS3 tablespace datafiles is unavailable. ORA-01157: cannot identify/lock data file 9 - see DBWR trace file ORA-01110: data file 9: '/u01/prod1/users3_02.dbf' 2) Determine the file# and original path/filename for each file in the database which will need to be restored and recovered. In sqlplus: SELECT FILE#,NAME FROM V$DATAFILE; For this example, datafile 9 has been lost, which is used for the USERS3 tablespace. The original file path is: /u01/prod1/users3_02.dbf. 3) Shutdown the database. sqlplus /nolog CONN / AS SYSDBA SHUTDOWN IMMEDIATE 4) Use sqlplus to startup the database to the mount state. Take the missing datafile offline. STARTUP MOUNT; ALTER DATABASE DATAFILE '/u01/prod1/users3_02.dbf' OFFLINE; ALTER DATABASE CREATE DATAFILE '/u01/prod1/users3_02.dbf'; ALTER DATABASE DATAFILE '/u01/prod1/users3_02.dbf' ONLINE; 5) The data contained within the missing 2nd datafile will then be reconstructed from the archivelog files data during the RMAN recovery process. rman target / RUN { ALLOCATE CHANNEL ch1 TYPE Disk; RECOVER TABLESPACE USERS3; ALTER DATABASE OPEN; RELEASE CHANNEL ch1; } QUIT 6) The database is now available for production use. 7) It is now recommended that a full database backup be done, in order to insure that all tablespace datafiles are fully backed up. ------------------------- Alternate Recovery Method 2 - without using RMAN ------------------------- 1) Startup the database with sqlplus. Make note of the errors which are reported in order to determine which datafile has been affected. For this example one of the USERS3 tablespace datafiles is unavailable. sqlplus /nolog CONN / AS SYSDBA STARTUP ORA-01157: cannot identify/lock data file 9 - see DBWR trace file ORA-01110: data file 9: '/u01/prod1/users3_02.dbf' 2) Determine the file# and original path/filename for each file in the database which will need to be restored and recovered. In sqlplus: SELECT FILE#,ONLINE_STATUS,ERROR FROM V$RECOVER_FILE; SELECT FILE#,NAME FROM V$DATAFILE where file#=9; For this example, datafile 9 has been lost, which is used for the USERS3 tablespace. The original file path is: /u01/prod1/users3_02.dbf. 3) Shutdown the database. sqlplus /nolog CONN / AS SYSDBA SHUTDOWN IMMEDIATE 4) Use sqlplus to startup the database to the mount state. Take the missing datafile offline. Re-create the missing datafile with the name which was originally used. (If media failure has occurred, the file may be renamed by adding the "AS 'new-filename'" clause. STARTUP MOUNT; ALTER DATABASE DATAFILE '/u01/prod1/users3_02.dbf' OFFLINE; ALTER DATABASE CREATE DATAFILE '/u01/prod1/users3_02.dbf'; ALTER DATABASE DATAFILE '/u01/prod1/users3_02.dbf' ONLINE; RECOVER TABLESPACE USERS3; ALTER DATABASE OPEN; 5) The database is now available for production use. 6) It is now recommended that a full database backup be done, in order to insure that all tablespace datafiles are fully backed up. # -------------------------------------------------------------------------------- # ----------------------- Scenario 14 - Missing Mirrored Online Redo Log Files Archivelog mode = Yes Situation: There are 3 online redo log file groups. Each group has 2 mirrored files, one of the mirrored online redo log files within each group has been lost. # -------------------------------------------------------------------------------- ------------------------- Preferred Recovery Method - using the OEM Console to create/drop logfile members ------------------------- 1) Startup the database (unless it is already started). 2) No error message occurs on the console when starting up the database with sqlplus because there remains one functional online redo log file within each online redo log file group. However the following errors are displayed within the /u01/bdump/prod1ALRT.LOG file: Errors in file /u01/bdump/prod1LGWR.TRC: ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: '/u01/prod1/redo01g1f2.log' ORA-27041: unable to open file OSD-04002: unable to open file O/S-Error: (OS 2) The system cannot find the file specified. (This message is repeated for each of the mirrored online redo log files which have been deleted.) 3) Use sqlplus to query the V$LOGFILE view to determine which logfile members are invalid: SELECT GROUP#,STATUS,MEMBER FROM V$LOGFILE; Note the names of each of the missing online redo log files: /u01/prod1/redo01g1f2.log /u01/prod1/redo02g2f2.log /u01/prod1/redo03g3f2.log 4) Log into the OEM Console using an account with DBA privileges. Click on the Storage/Redo Log Groups folder in the window on the left side. 5) Right-click on the 1st redo log group containing a missing logfile and select the "Switch Logfile" menu item - if the logfile group is the current logfile group. 6) Double-click on the 1st redo log group, then click on the small square box to the left of the missing log file member. The trash can icon in the lower left corner of the window will become highlighted. Right-click within the small square box to the left of the missing log file member, and select the "Remove" menu item. Click the "Apply" button at the bottom of the window. 7) Create a new redo log file member for this group by clicking on the blank line below the last redo logfile member, then enter a new filename and directory. Click the "Apply" button at the bottom of the window. 8) Repeat steps 5 thru 7 for each logfile group which has a missing logfile member. ------------------------- Alternate Recovery Method 2 - using the OEM Console to create/drop logfile groups ------------------------- 1) Startup the database (unless it is already started). 2) No error message occurs on the console when starting up the database with sqlplus because there remains one functional online redo log file within each online redo log file group. However the following errors are displayed within the /u01/bdump/prod1ALRT.LOG file: Errors in file /u01/bdump/prod1LGWR.TRC: ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: '/u01/prod1/redo01g1f2.log' ORA-27041: unable to open file OSD-04002: unable to open file O/S-Error: (OS 2) The system cannot find the file specified. (This message is repeated for each of the mirrored online redo log files which have been deleted.) 3) Use sqlplus to query the V$LOGFILE view to determine which logfile members are invalid: SELECT GROUP#,STATUS,MEMBER FROM V$LOGFILE; Note the names of each of the missing online redo log files: /u01/prod1/redo01g1f2.log /u01/prod1/redo02g2f2.log /u01/prod1/redo03g3f2.log 4) Log into the OEM Console using an account with DBA privileges. Click on the Storage/Redo Log Groups folder in the window on the left side. 5) Create 3 new redo log groups to replace the 3 groups which contain missing logfile members: Right click on the Redo Log Groups folder, select the "Create..." menu item. Re-name each logfile member name, directory and size as needed. Make certain to create a minimum of 2 logfile members for each group, with each member located on a different drive. Click on the "Create" button. 6) Right-click on any of the redo log groups and select the "Switch Logfile" menu item, repeat as necessary until the Current logfile group is one of the newly created logfile groups. 7) Right-click on each of the original logfile groups containing missing members, and select the "Remove" menu item. ------------------------- Alternate Recovery Method 2 - using sqlplus to create new logfile groups and drop old groups ------------------------- 1) Startup the database (unless it is already started). sqlplus /nolog CONN / AS SYSDBA STARTUP No error message occurs on the console when starting up the database with sqlplus because there remains one functional online redo log file within each online redo log file group. However the following errors are displayed within the /u01/bdump/prod1ALRT.LOG file: Errors in file /u01/bdump/prod1LGWR.TRC: ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: '/u01/prod1/redo01g1f2.log' ORA-27041: unable to open file OSD-04002: unable to open file O/S-Error: (OS 2) The system cannot find the file specified. (This message is repeated for each of the mirrored online redo log files.) 2) Query the V$LOGFILE view to determine which logfile members are invalid: SELECT GROUP#,STATUS,MEMBER FROM V$LOGFILE; Note the names of each of the missing online redo log files: /u01/prod1/redo01g1f2.log /u01/prod1/redo02g2f2.log /u01/prod1/redo03g3f2.log 3) Using sqlplus, create 3 more online redo logfile groups, containing 2 mirrored datafiles in each group. ALTER DATABASE ADD LOGFILE GROUP 4 ('/u01/prod1/redo04g4f1.log','/u02/prod1/redo04g4f2.log') SIZE 10M; ALTER DATABASE ADD LOGFILE GROUP 5 ('/u01/prod1/redo05g5f1.log','/u02/prod1/redo05g5f2.log') SIZE 10M; ALTER DATABASE ADD LOGFILE GROUP 6 ('/u01/prod1/redo06g6f1.log','/u02/prod1/redo06g6f2.log') SIZE 10M; 4) Switch the current logfile group until none of the original 3 logfile groups are being used. ALTER SYSTEM SWITCH LOGFILE; ALTER SYSTEM SWITCH LOGFILE; ALTER SYSTEM SWITCH LOGFILE; 5) Drop each of the original logfile groups: ALTER DATABASE DROP LOGFILE GROUP 1; ALTER DATABASE DROP LOGFILE GROUP 2; ALTER DATABASE DROP LOGFILE GROUP 3; 6) Query the V$LOGFILE view to verify that there are no additional invalid logfile members. SELECT GROUP#,STATUS,MEMBER FROM V$LOGFILE; # -------------------------------------------------------------------------------- # ----------------------- Scenario 15 - Loss of a Control File and Read-Only Tablespace Archivelog mode = Yes Situation: The database has been backed up, along with the controlfile. All of the controlfiles and the datafile for a read-only tablespace have been lost. Note: The backups have been done by specifying the RMAN "NOEXCLUDE" option in order to guarantee that Read-Only tablespaces are always backed up. # -------------------------------------------------------------------------------- ------------------------- Preferred Recovery Method - using RMAN restore with a recovery catalog ------------------------- If any one of the binary control files still exist, do not perform an RMAN restore. See info listed in Scenario #3 concerning restoring missing controlfiles. Only use RMAN to restore lost control files if all binary copies of the control files have been lost. Note: In order to prepare for this recovery scenario, retrieve the /backup/prod1_disaster_recovery.txt file produced during the most recent backup. Customize the following RMAN restore commands in order to manually add the names of Read-Only tablespaces to the recovery process. This recovery example uses a Read-Only tablespace named QUERY_DATA. 1) Make sure that the database is shut down. sqlplus /nolog conn / as sysdba shutdown immediate quit 2) Execute RMAN from the server which needs the restore to connect to the recovery catalog in the OMS server then perform the restore of the controlfiles and database. rman target / connect catalog rman/rt-ut@oem1.world run { startup nomount; allocate channel ch1 type Disk; restore controlfile; sql 'alter database mount'; restore database; restore tablespace QUERY_DATA; recover database; sql 'alter database open resetlogs'; } 3) Prior to performing the next RMAN backup, it will be necessary to reset the database information in the RMAN catalog. Select the menu item Tools/Database Tools/Backup Management/Maintenance... Click the "Next" button. Click the "Reset database" radio button. Click the "Next" button. Click the "Finish" button. Click the "Ok" button on the next 2 windows which pop up. 4) Perform another full RMAN database backup since the previous backups are now unusable due to the database now having been set to a different database incarnation number. 5) Once the RMAN backup has completed, the database will then be ready for production use. ------------------------- Alternate Recovery Method 2 - using RMAN restore - without a recovery catalog [NOT POSSIBLE] Note: This is not possible because RMAN needs the info in the non-existent controlfile to perform the controlfile restore! -------------------------