Updated Last – 14-01-2010 # ----------------------- Database Recovery Scenarios: -------------------------Summary of Scenarios: (***NOT IN ORDER***) -- Redo Log Group Scenario 1 - Recovering After Losing a Member of a Multiplexed Online Redo Log Group Scenario 2 - Recovering After the Loss of All Members of an Online Redo Log Group Scenario 3 - Loss of INACTIVE Online Redo Log Group Scenario 4 - Loss of CURRENT Online Redo Log Group Scenario 5 - Recovering After the Loss of All Members of an Online Redo Log Group Using RMAN Scenario 6 - Recovering After the Loss of All Members of an Online Redo Log Group Using RMAN with Catalog with CommVault Scenario 33 - Create new logfile groups and drop old groups -- Control file Scenario 7 - Loss of control file Scenario 8 - Recovering After a lost of all control files using RMAN Scenario 9 - Recovering After a lost of all control files using control file backup to trace file Scenario 10 - Recovering After a lost of all control files using RMAN with Catalog with CommVault Scenario 11 - Recovering After a lost of all control files and spfile using NO Catalog Scenario 12 - Recovering After a lost of all control files and spfile using NO Catalog with CommVault --Non-System Tablespace/Datafiles Scenario 13 - One or more of the non-SYSTEM database datafiles have been destroyed due to media failure using RMAN with Catalog Scenario 14 - One or more of the non-SYSTEM database datafiles have been destroyed due to media failure using RMAN with Catalog with CommVault Scenario 17 - Missing Data File - non-SYSTEM tablespace file Scenario 18 - Loss of Non-Essential Datafile When Database is Down Scenario 19 - Recover a Lost Datafile with No Backup with RMAN with Catalog Scenario 20 - Recover a Lost Datafile with No Backup without RMAN Scenario 21 - Recover a Lost Datafile with No Backup with RMAN with Catalog with CommVault (***AWAITING RESPONSE FROM COMMVAULT***)(They don’t know…) Scenario 29 - Loss of an Online Undo Segment Datafile (Open or Closed Database) with RMAN with Catalog
1
Scenario 30 - Loss of an Online Undo Segment Datafile (Open or Closed Database) with RMAN with NO Catalog Scenario 31 - Loss of an Online Undo Segment Datafile (Open or Closed Database) without RMAN Scenario 32 - Failure during Open Database - tablespace in backup mode with RMAN with Catalog --SYSTEM Tablespace/Datafile Scenario 15 - Loss of a SYSTEM tablespace datafile using RMAN with Catalog Scenario 16 - Loss of a SYSTEM tablespace datafile without using RMAN --User Errors Scenario 22 - Recover from User Errors - dropped table or deleted data with RMAN with Catalog Scenario 23 - Recover from User Errors - dropped table or deleted data with RMAN with NO Catalog Scenario 24 - Recover from User Errors - dropped table or deleted data manual data entry Scenario 25 - Recover from User Errors - dropped table or deleted data – Flashback Drop Scenario 26 - Recover from User Errors - dropped table or deleted data – Flashback Table and Query Scenario 27 - Recover from User Errors - dropped table or deleted data – Using Log Miner Scenario 28 - Recover from User Errors - dropped table or deleted data – Flashback Database --Archive Logs Scenario 34 - Recover archive log using RMAN with Catalog Scenario 35 - Recover archive log using RMAN with Catalog with CommVault --Duplicate Database Scenario 36 – Duplicate Database using RMAN with Catalog Scenario 37 – Duplicate Database using RMAN with Catalog with CommVault (***AWAITING RESPONSE FROM COMMVAULT***)(This fails memory script takes hours to run, it is mounted and trips….) --Block Corruption Scenario 38 – Check and repair block corruption using RMAN with Catalog
2
--Background Information If a media failure has affected the online redo logs of a database, then the appropriate recovery procedure depends on the following: • • •
The configuration of the online redo log: mirrored or non-mirrored The type of media failure: temporary or permanent The types of online redo log files affected by the media failure: current, active, unarchived, or inactive
--Views Select member from v$logfile; Select * from v$log; Select * from v$archived_log; --DBA Stuff Add 1 member to group - mirror alter database ADD logfile member 'E:\ORACLE\ORADATA\BACKUPS\REDO02_2.LOG' to group 2; Drop member alter database DROP logfile member 'E:\ORACLE\ORADATA\BACKUPS\REDO02_2.LOG'; Will generate error message ... ORA-01609: log 1 is the current log for thread 1 - cannot drop members keep switching alter system switch logfile; Restore Archive log restore archivelog from logseq=42 until logseq=42; Relationship between SCN and log files Select * from v$log_history -each log file - has series change no's from first to next change... -based on archive log format Archived Redo Log File Conventions in Oracle
3
Table 7-1 Archived Redo Log Filename Format Parameters Archive log Format %s log sequence number %S log sequence number, zero filled %t thread number %T thread number, zero filled %a activation ID %d database ID %r resetlogs ID that ensures unique names are constructed for the archived log files across multiple incarnations of the database *.log_archive_format='ARC%S_%D.%T' e.g. ARC00065_1255702348 Ref: select dbid from v$database; SQL> select dbid from v$database; DBID ---------1255702348 Displays V$LOG status information that can be crucial in a recovery situation involving online redo logs Status
Description
UNUSED
The online redo log has never been written to.
CURRENT
The online redo log is active, that is, needed for instance recovery, and it is the log to which the database is currently writing. The redo log can be open or closed.
ACTIVE
The online redo log is active, that is, needed for instance recovery, but is not the log to which the database is currently writing.It may be in use for block recovery, and may or may not be archived.
CLEARING
The log is being re-created as an empty log after an ALTER DATABASE CLEAR LOGFILE statement. After the log is cleared, then the status changes to UNUSED.
CLEARING_CURRENT The current log is being cleared of a closed thread. The log can stay in this status if there is some failure
4
Status
Description in the switch such as an I/O error writing the new log header.
INACTIVE
The log is no longer needed for instance recovery. It may be in use for media recovery, and may or may not be archived.
#------------------------Scenario 1 - Recovering After Losing a Member of a Multiplexed Online Redo Log Group #------------------------If the online redo log of a database is multiplexed, and if at least one member of each online redo log group is not affected by the media failure, then the database continues functioning as normal, but error messages are written to the log writer trace file and the alert_SID.log of the database. Solve the problem by taking one of the following actions: •
•
If the hardware problem is temporary, then correct it. The log writer process accesses the previously unavailable online redo log files as if the problem never existed. If the hardware problem is permanent, then drop the damaged member and add a new member by using the following procedure.
Step1 SELECT * FROM V$LOG;
Group 1 & 2 inactive Rename group 1 plus 1 member Step 2 Locate the filename of the damaged member in V$LOGFILE. The status is INVALID if the file is inaccessible: SELECT GROUP#, STATUS, MEMBER FROM V$LOGFILE WHERE STATUS='INVALID';
Step 3 Drop the damaged member
5
ALTER DATABASE DROP LOGFILE MEMBER 'E:\ORACLE\ORADATA\BACKUPS\REDO01.LOG'; If u get error ORA-01609 ALTER SYSTEM SWITCH LOGFILE; Step 4 Add a new member to the group. ALTER DATABASE ADD LOGFILE MEMBER 'E:\ORACLE\ORADATA\BACKUPS\REDO01.LOG' TO GROUP 1; If the file you want to add already exists, then it must be the same size as the other group members, and you must specify REUSE. For example: ALTER DATABASE ADD LOGFILE MEMBER '/oracle/oradata/trgt/redo02b.log' REUSE TO GROUP 2; #------------------------Scenario 2 - Recovering After the Loss of All Members of an Online Redo Log Group #------------------------If a media failure damages all members of an online redo log group, then different scenarios can occur depending on the type of online redo log group affected by the failure and the archiving mode of the database. If the damaged log group is active, then it is needed for crash recovery; otherwise, it is not. Inactive It is not needed for Clear the archived or unarchived group. crash recovery Active
It is needed for crash recovery
Attempt to issue a checkpoint and clear the log; if impossible, then you must restore a backup and perform incomplete recovery up to the most recent available redo log.
Current It is the log that the Attempt to clear the log; if impossible, then you database is must restore a backup and perform incomplete currently writing to recovery up to the most recent available redo log. Step 1 Your first task is to determine whether the damaged group is active or inactive. SELECT GROUP#, STATUS, MEMBER FROM V$LOGFILE;
6
Step 2 Determine which groups are active SELECT GROUP#, MEMBERS, STATUS, ARCHIVED FROM V$LOG;
If the affected group is inactive, follow the procedure in Losing an Inactive Online Redo Log Group. If the affected group is active (as in the preceding example), then follow the procedure in "Losing an Active Online Redo Log Group". # -------------------------------------------------------------------------------Scenario 3 - Loss of INACTIVE Online Redo Log Group # -------------------------------------------------------------------------------If all members of an online redo log group with INACTIVE status are damaged, then the procedure depends on whether you can fix the media problem that damaged the inactive redo log group. Temporary Fix the problem. LGWR can reuse the redo log group when required. Permanent The damaged inactive online redo log group eventually halts normal database operation. Reinitialize the damaged group manually by issuing the ALTER DATABASE CLEAR LOGFILE statement as described in this section.
Clearing Inactive, Archived Redo You can clear an inactive redo log group when the database is open or closed. The procedure depends on whether the damaged group has been archived. To clear an inactive, online redo log group that has been archived, use the following procedure:
7
If the database is shut down, then start a new instance and mount the database: STARTUP MOUNT Initialize the damaged log group. For example, to clear redo log group 1 issue the following statement: ALTER DATABASE CLEAR LOGFILE GROUP 1; Clearing Inactive, Not-Yet-Archived Redo To clear an inactive, online redo log group that has not been archived, use the following procedure: If the database is shut down, then start a new instance and mount the database: STARTUP MOUNT Clear the log using the UNARCHIVED keyword. For example, to clear log group 1, issue: ALTER DATABASE CLEAR LOGFILE UNARCHIVED GROUP 1; If there is an offline datafile that requires the cleared log to bring it online, then the keywords UNRECOVERABLE DATAFILE are required. The datafile and its entire tablespace have to be dropped because the redo necessary to bring it online is being cleared, and there is no copy of it. For example, enter: ALTER DATABASE CLEAR LOGFILE UNARCHIVED GROUP 1 UNRECOVERABLE DATAFILE; Immediately back up the whole database with an operating system utility, so that you have a backup you can use for complete recovery without relying on the cleared log group rman Target SYS/BACKUPS@BACKUPS catalog Rmancat/Rmancat@Rmancat Backup database plus archivelog; Backup archivelog all; Back up the database's control file with the ALTER DATABASE statement. For example, enter ALTER DATABASE BACKUP CONTROLFILE TO 'G:\Oracle_Backups\Backups\cf_backup.f';
8
Failure of CLEAR LOGFILE Operation The ALTER DATABASE CLEAR LOGFILE statement can fail with an I/O error due to media failure when it is not possible to: • •
Relocate the redo log file onto alternative media by re-creating it under the currently configured redo log filename Reuse the currently configured log filename to re-create the redo log file because the name itself is invalid or unusable (for example, due to media failure)
In these cases, the ALTER DATABASE CLEAR LOGFILE statement (before receiving the I/O error) would have successfully informed the control file that the log was being cleared and did not require archiving. The I/O error occurred at the step in which the CLEAR LOGFILE statement attempts to create the new redo log file and write zeros to it. This fact is reflected in V$LOG.CLEARING_CURRENT. # -------------------------------------------------------------------------------Scenario 4 - Loss of CURRENT Online Redo Log Group # -------------------------------------------------------------------------------If the database is still running and the lost active redo log is not the current log, then issue the ALTER SYSTEM CHECKPOINT statement. If successful, then the active redo log becomes inactive, and you can follow the procedure in "Losing an Inactive Online Redo Log Group". If unsuccessful, or if your database has halted, then perform one of procedures in this section, depending on the archiving mode. The current log is the one LGWR is currently writing to. If a LGWR I/O fails, then LGWR terminates and the instance crashes. In this case, you must restore a backup, perform incomplete recovery, and open the database with the RESETLOGS option. To recover from loss of an active online log group in NOARCHIVELOG mode: If the media failure is temporary, then correct the problem so that the database can reuse the group when required. Restore the database from a consistent, whole database backup (datafiles and control files) Mount the database: STARTUP MOUNT;
9
Because online redo logs are not backed up, you cannot restore them with the datafiles and control files. In order to allow the database to reset the online redo logs, you must first mimic incomplete recovery: RECOVER DATABASE UNTIL CANCEL; Open the database using the RESETLOGS option: ALTER DATABASE OPEN RESETLOGS; Open the database using the RESETLOGS option: ALTER DATABASE OPEN RESETLOGS; To recover from loss of an active online redo log group in ARCHIVELOG mode: If the media failure is temporary, then correct the problem so that the database can reuse the group when required. If the media failure is not temporary, then use the following procedure. Begin incomplete media recovery, recovering up through the log before the damaged log Ensure that the current name of the lost redo log can be used for a newly created file. If not, then rename the members of the damaged online redo log group to a new location ALTER DATABASE RENAME FILE "?/oradata/trgt/redo01.log" TO "/tmp/redo01.log"; Open the database using the RESETLOGS option: ALTER DATABASE OPEN RESETLOGS; #------------------------Scenario 5 - Recovering After the Loss of All Members of an Online Redo Log Group Using RMAN with Catalog #------------------------Step 1 Full Backup rman Target SYS/BACKUPS@BACKUPS catalog Rmancat/Rmancat@Rmancat Backup database plus archivelog; Backup archivelog all;
10
Step 2 select member from v$Logfile;
We can simulate this scenario by deleting all the online redo log files at the OS level. ORA-00313 Errors in file e:\oracle\admin\backups\bdump\backups_lgwr_3216.trc: ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: 'E:\ORACLE\ORADATA\BACKUPS\REDO01.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. ORA-00312: online log 1 thread 1: 'E:\ORACLE\ORADATA\BACKUPS\REDO01_2.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. Step 3 Using RMAN we can recover from this error by restoring the database from the backup and recovering to the last available archived redo logfile. From the alert log we can obtain the last archived file in our case it is sequence 0 as the error shows that it fails to archive the log file sequence 21. STARTUP MOUNT; select * from v$Log;
11
rman Target SYS/BACKUPS@BACKUPS catalog Rmancat/Rmancat@Rmancat RUN { SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALLOCATE CHANNEL ch1 TYPE Disk; RESTORE DATABASE; RECOVER DATABASE; Sql ‘ALTER DATABASE OPEN’; } OR rman Target SYS/BACKUPS@BACKUPS catalog Rmancat/Rmancat@Rmancat RUN { SHUTDOWN IMMEDIATE; STARTUP MOUNT; set until sequence XX thread 1; ALLOCATE CHANNEL ch1 TYPE Disk; RESTORE DATABASE; RECOVER DATABASE; ALTER DATABASE OPEN RESETLOGS; } #------------------------Scenario 6 - Recovering After the Loss of All Members of an Online Redo Log Group Using RMAN with Catalog with CommVault #------------------------Step 1 Full Backup
12
Step 2 Shutdown immediate Deleting all redo log files Startup mount
13
14
Need to enter details of Catalog Connect Script Preview Run { allocate channel ch1 type 'sbt_tape' PARMS="BLKSIZE=0"; restore database ; recover database ; } No log files….. Outcome of this, it can’t find any redo log files… This will not restore redo log file, you need to create them. ALTER DATABASE OPEN; #------------------------Scenario 7 - Recovering After a lost of single control file #------------------------Step 1 Shut down the database. Delete one control file Step 2 Startup open; Check log file; RC1: Thread not mounted Thu Oct 08 16:53:20 2009 ORA-00202: controlfile: 'e:\oracle\oradata\BACKUPS\control02.ctl' ORA-27041: unable to open file OSD-04002: unable to open file O/S-Error: (OS 2) The system cannot find the file specified. Step 3 Shut down the database. 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.) Rename the copy of the control file with the name of the missing control file name.
15
Startup open; Should open assuming no issues. #------------------------Scenario 8 - Recovering After a lost of all control files using RMAN with Catalog #------------------------Step 1 Shut down the database. Rename all control files Step 2 rman Target SYS/BACKUPS@BACKUPS catalog Rmancat/Rmancat@Rmancat RUN { STARTUP NOMOUNT; ALLOCATE CHANNEL ch1 TYPE Disk; RESTORE CONTROLFILE; (Or RESTORE CONTROLFILE FROM AUTOBACKUP) SQL 'ALTER DATABASE MOUNT'; RECOVER DATABASE; SQL 'ALTER DATABASE OPEN RESETLOGS'; } 2 outcomes: Successful: (depends on the status of the database, if its clean)(quick) Prior to performing the next RMAN backup, it will be necessary to reset the database information in the RMAN catalog. Full Backup whenever you issue an ALTER DATABASE OPEN RESETLOGS you must always reset the database in RMAN? RMAN-03002: failure of backup command at 09/17/2007 14:52:42 RMAN-06004: ORACLE error from recovery catalog database: RMAN-20003: target database incarnation not found in recovery catalog rman Target SYS/BACKUPS@BACKUPS catalog Rmancat/Rmancat@Rmancat RESET DATABASE; Full Backup – new baseline
16
rman Target SYS/BACKUPS@BACKUPS catalog Rmancat/Rmancat@Rmancat Backup database plus archivelog; Backup archivelog all; Failure: (maybe can of worms…) ORA-01547 RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL; When prompted for the non-existing logfile type 'CANCEL', then open the database with 'resetlogs'. If this does not work the datafiles definitely need more archivelog information to become consistent. RESTORE DATABASE; RECOVER DATABASE UNTIL SEQUENCE xxxx; ALTER DATABASE OPEN RESETLOGS; LIST ARCHIVELOG ALL; LIST BACKUP; But even the RESETLOGS won’t work yet – we need to do some recovery first. Well let’s finish up. First off, Oracle actually needs to look at the ONLINE logs. By the way, there’s one thing to be careful of here… check it out: col member format a40 select l.status, member from v$logfile inner join v$log l using (group#); (LOOK FOR CURRENT LOG) recover database using backup controlfile until cancel; FILENAME /u04/oracle/oradata/jt10g/redo02.log May not work (try using another log file - view is showing you the BACKUP controlfile – see view above) recover database using backup controlfile until cancel; /u04/oracle/oradata/jt10g/redo03.log Looks like we still need to apply the last log. recover database using backup controlfile until cancel;
17
point to filename /u04/oracle/product/10.2.0/db_1/dbs/arch1_6_621627183.dbf Media recovery complete alter database open resetlogs; #------------------------Scenario 9 - Recovering After a lost of all control files using control file backup to trace file #------------------------Step 1 Assume you have issued command and saved the file ALTER DATABASE BACKUP CONTROLFILE TO TRACE; ------------------------TRACEFILE ------------------------Below are two sets of SQL statements, each of which creates a new control file and uses it to open the database. A) The first set opens the database with the NORESETLOGS option and should be used only if the current versions of all online logs are available. B) The second set opens the database with the RESETLOGS option and should be used if online logs are unavailable. Step 2 Copy the trace file script to /backup directory to rebuild the control file. For the purposes of this example, the file will be renamed as BACKUPS_rebuild_controlfile.sql.
18
BACKUPS_rebuild_controlfile.sql. ------------------------------------------------------------------------------------------------------STARTUP MOUNT CREATE CONTROLFILE REUSE DATABASE "BACKUPS" NORESETLOGS ARCHIVELOG -- SET STANDBY TO MAXIMIZE PERFORMANCE MAXLOGFILES 5 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 1 MAXLOGHISTORY 226 LOGFILE GROUP 1 'E:\ORACLE\ORADATA\BACKUPS\REDO01.LOG' SIZE 100M, GROUP 2 'E:\ORACLE\ORADATA\BACKUPS\REDO02.LOG' SIZE 100M, GROUP 3 'E:\ORACLE\ORADATA\BACKUPS\REDO03.LOG' SIZE 100M -- STANDBY LOGFILE DATAFILE 'E:\ORACLE\ORADATA\BACKUPS\SYSTEM01.DBF', 'E:\ORACLE\ORADATA\BACKUPS\UNDOTBS01.DBF', 'E:\ORACLE\ORADATA\BACKUPS\INDX01.DBF', 'E:\ORACLE\ORADATA\BACKUPS\TOOLS01.DBF', 'E:\ORACLE\ORADATA\BACKUPS\USERS01.DBF' CHARACTER SET WE8ISO8859P1; ------------------------------------------------------------------------------------------------------Step 3 set ORACLE_SID=BACKUPS sqlplus /nolog CONN SYS/Backups AS SYSDBA Select * from v$instance; SHUTDOWN IMMEDIATE; Rename ALL control files E.G. control01.xxx @ E:\ORACLE\ORADATA\BACKUPS\BACKUPS_rebuild_controlfile.sql SHUTDOWN IMMEDIATE; STARTUP OPEN; #------------------------Scenario 10 - Recovering After a lost of all control files using RMAN with Catalog with CommVault #------------------------set ORACLE_SID=BACKUPS sqlplus /nolog
19
CONN SYS/Backups AS SYSDBA Shutdown immediate Deleting all control files Startup nomount;
20
21
run { allocate channel ch1 type 'sbt_tape' PARMS="BLKSIZE=0"; restore controlfile to '
/ctrlFile' ; replicate controlfile from '/ctrlFile'; sql 'alter database mount'; release channel ch1; } Copies control file first to temp location on the local host C:\Program Files\CommVault\Galaxy\Base\Temp Then copies it over… ALTER DATABASE OPEN RESETLOGS; Successful: Failure: ORA-01152 ORA-01110 It could be your control files are out of date with rest of the database ! Recovery Make sure it is in MOUNT;
22
Script Preview run { allocate channel ch1 type 'sbt_tape' PARMS="BLKSIZE=0"; recover database ; } Full Backup You can tick both Restore Control File & Recover same time…. #------------------------Scenario 11 - Recovering After a lost of all control files and spfile using NO Catalog #------------------------set ORACLE_SID=BACKUPS sqlplus /nolog CONN SYS/Backups AS SYSDBA Need to know select dbid from v$database; DBID ---------2073096330 Shutdown immediate; Deleting all control files ***MAKE SURE NO SPACES*** rman Target SYS/BACKUPS@BACKUPS NOcatalog SET DBID=2073096330; Startup nomount FORCE; SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'G:\Oradata\Recovery_Area\backups\Rman_Backups\Ora_cf%F'; restore spfile to 'E:\oracle\ora92\database\INITBackups.ora' from autobackup; restore controlfile from 'g:\Oradata\Recovery_Area\backups\Rman_Backups\ ORA_CFC-2073096330-20091015-01'; alter database mount; recover database; alter database open resetlogs; backup database; Failure: RMAN-06172 (if u get this check there are no spaces in path !!!)
23
If so, check setting SHOW ALL; CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'G:\Oradata\Recovery_Area\backups\Rman_Backups\Ora_cf%F'; Full Backup rman Target SYS/BACKUPS@BACKUPS catalog Rmancat/Rmancat@Rmancat Backup database plus archivelog; Backup archivelog all; Check location g:\Oradata\Recovery_Area\backups\Rman_Backups\ Look for the last file
#------------------------Scenario 12 - Recovering After a lost of all control files and spfile using NO Catalog with CommVault #------------------------Tried 2 ways : first way incorrect, second way correct First way: Full Backup with CommVault set ORACLE_SID=BACKUPS sqlplus /nolog CONN SYS/Backups AS SYSDBA Shutdown immediate; Deleting all control files Startup nomount FORCE;
24
25
Restore control files first…
26
Script Preview SET DBID ; run { allocate channel ch1 type 'sbt_tape' PARMS="BLKSIZE=0"; restore control ; } RMAN Log Rman Script: [SET DBID 2073096330; run { allocate channel ch1 type 'sbt_tape' PARMS="BLKSIZE=262144,ENV=(CV_mmsApiVsn=2,CV_channelPar=ch1,T hreadCommandLine= RESTORE -jm 10 -a 2:0 -cl 10 -ins 50 -at 0 -j 104254 -bal 0 -bap 0 -rap 0 -rcp 0 -mav 0 -ms 1 -p 1 -cn glasbs2 -vm Instance001 -vm glasbs2.vws.vh20.net)" TRACE 0; restore controlfile ; sql 'alter database mount'; } 27
exit; ] Rman Log:[ Recovery Manager: Release 9.2.0.1.0 - Production Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved. RMAN> RMAN> connected to target database: BACKUPS (not mounted) using target database controlfile instead of recovery catalog RMAN> executing command: SET DBID RMAN> 2> 3> 4> 5> 6> 7> allocated channel: ch1 channel ch1: sid=10 devtype=SBT_TAPE channel ch1: CommVault Systems for Oracle: Version 7.0.0(BUILD76) Starting restore at 16-OCT-09 released channel: ch1 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of restore command at 10/16/2009 09:39:44 RMAN-06563: controlfile or SPFILE must be restored using FROM AUTOBACKUP RMAN> Recovery Manager complete. ]RMAN> Recovery Manager complete. ] ***NOT SURE WHY ‘From Autobackup’ is disabled*** Could it be when you setup RMAN with no catalog ??? If I create within CommVault to back up a database, but do not specify catalog, I get this:
28
This is a correct message for Oracle 8. Since Oracle 9 we can use the controlfile autobackup feature. Second Way: Full Backup with CommVault set ORACLE_SID=BACKUPS sqlplus /nolog CONN SYS/Backups AS SYSDBA Shutdown immediate; Deleting all control files Startup nomount FORCE;
Lets restore control files first
29
30
Script Preview SET DBID 2073096330; run { allocate channel ch1 type 'sbt_tape' PARMS="BLKSIZE=262144"; restore controlfile to '/ctrlFile' from autobackup ; replicate controlfile from '/ctrlFile'; sql 'alter database mount'; release channel ch1; } Takes longer…. Successful: It’s mounted: Next recover
31
32
Script Preview run { allocate channel ch1 type 'sbt_tape' PARMS="BLKSIZE=262144"; recover database ; } Successful: It’s open:
33
#------------------------Scenario 13 - One or more of the non-SYSTEM database datafiles have been destroyed due to media failure using RMAN with Catalog #------------------------Step 1 Full Backup rman Target SYS/BACKUPS@BACKUPS catalog Rmancat/Rmancat@Rmancat Backup database plus archivelog; Backup archivelog all; SELECT FILE#,NAME FROM V$DATAFILE;
USERS tablespace is unavailable. The USERS tablespace will need to be restored from a backup to a different mount point. File#5 Step 2 set ORACLE_SID=BACKUPS sqlplus /nolog CONN SYS/Backups AS SYSDBA ALTER TABLESPACE USERS OFFLINE IMMEDIATE; SHUTDOWN IMMEDIATE EXIT #Delete file Step 3 Restore and recover the affected tablespace datafile while renaming it to use a different location to avoid the failed media. rman Target SYS/BACKUPS@BACKUPS catalog Rmancat/Rmancat@Rmancat RUN { STARTUP MOUNT; ALLOCATE CHANNEL ch3 TYPE Disk; SET NEWNAME FOR DATAFILE 5 TO ‘E:\ORACLE\ORADATA\BACKUPS\NEWMEDIA\USERS01.DBF'; 34
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 ch3; } QUIT #------------------------Scenario 14 - One or more of the non-SYSTEM database datafiles have been destroyed due to media failure using RMAN with Catalog with CommVault #------------------------Full Backup with CommVault set ORACLE_SID=BACKUPS sqlplus /nolog CONN SYS/Backups AS SYSDBA ALTER TABLESPACE USERS OFFLINE IMMEDIATE; SHUTDOWN IMMEDIATE; #Delete file STARTUP MOUNT; EXIT
35
Options Restore Data & Recover - selected Open DB – not selected (options tab) Script generated Rman Script: [run { allocate channel ch1 type 'sbt_tape' PARMS="BLKSIZE=262144,ENV=(CV_mmsApiVsn=2,CV_channelPar=ch1,T hreadCommandLine= RESTORE -jm 142 -a 2:0 -cl 10 -ins 50 -at 0 -j 104265 -bal 0 -bap 0 -rap 0 -rcp 0 -mav 0 -ms 1 -p 2 -cn glasbs2 -vm Instance001 -vm glasbs2.vws.vh20.net)" TRACE 0; restore ( tablespace 'USERS' ) ; recover tablespace 'USERS' ; } exit; ] Rman Log:[ Recovery Manager: Release 9.2.0.1.0 - Production Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved. RMAN> connected to recovery catalog database
36
RMAN> connected to target database: BACKUPS (DBID=2073096330) RMAN> 2> 3> 4> 5> 6> 7> 8> allocated channel: ch1 channel ch1: sid=9 devtype=SBT_TAPE channel ch1: CommVault Systems for Oracle: Version 7.0.0(BUILD76) Starting restore at 16-OCT-09 channel ch1: starting datafile backupset restore channel ch1: specifying datafile(s) to restore from backup set restoring datafile 00005 to E:\ORACLE\ORADATA\BACKUPS\NEWMEDIA\USERS01.DBF channel ch1: restored backup piece ( Appended) piece handle=1jkrukvc_1_1 tag=TAG20091016T125156 params=NULL channel ch1: restore complete Finished restore at 16-OCT-09 Starting recover at 16-OCT-09 starting media recovery media recovery complete Finished recover at 16-OCT-09 released channel: ch1 RMAN> Recovery Manager complete. ] Still MOUNTED Restore Recover – selected Open DB – reset logs – (no I think…..) (options tab) rman Script: [run { allocate channel ch1 type 'sbt_tape' PARMS="BLKSIZE=262144,ENV=(CV_mmsApiVsn=2,CV_channelPar=ch1,T hreadCommandLine= RESTORE -jm 142 -a 2:0 -cl 10 -ins 50 -at 0 -j 104266 -bal 0 -bap 0 -rap 0 -rcp 0 -mav 0 -ms 1 -p 2 -cn glasbs2 -vm Instance001 -vm glasbs2.vws.vh20.net)" TRACE 0; recover database ; sql "alter database open"; } exit; ] Rman Log:[ Recovery Manager: Release 9.2.0.1.0 - Production Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved. RMAN> connected to recovery catalog database RMAN> connected to target database: BACKUPS (DBID=2073096330)
37
RMAN> 2> 3> 4> 5> 6> 7> allocated channel: ch1 channel ch1: sid=11 devtype=SBT_TAPE channel ch1: CommVault Systems for Oracle: Version 7.0.0(BUILD76) Starting recover at 16-OCT-09 starting media recovery media recovery complete Finished recover at 16-OCT-09 sql statement: alter database open released channel: ch1 RMAN> Recovery Manager complete. ] #------------------------Scenario 15 - Loss of a SYSTEM tablespace datafile using RMAN with Catalog #------------------------Step 1 Full Backup rman Target SYS/BACKUPS@BACKUPS catalog Rmancat/Rmancat@Rmancat Backup database plus archivelog; Backup archivelog all; SELECT FILE#,NAME FROM V$DATAFILE;
Step 2 set ORACLE_SID=BACKUPS sqlplus /nolog CONN SYS/Backups AS SYSDBA SHUTDOWN IMMEDIATE EXIT #Delete file
38
rman Target SYS/BACKUPS@BACKUPS catalog Rmancat/Rmancat@Rmancat RUN { STARTUP MOUNT; ALLOCATE CHANNEL ch3 TYPE Disk; RESTORE datafile 1; RECOVER datafile 1; ALTER DATABASE OPEN; RELEASE CHANNEL ch3; } QUIT Full Backup #------------------------Scenario 16 - Loss of a SYSTEM tablespace datafile without using RMAN #------------------------Step 1 Full Backup rman Target SYS/BACKUPS@BACKUPS catalog Rmancat/Rmancat@Rmancat Backup database plus archivelog; Backup archivelog all; set ORACLE_SID=BACKUPS sqlplus /nolog CONN SYS/Backups AS SYSDBA SHUTDOWN IMMEDIATE #Delete file STARTUP MOUNT; Step 2 Find the file numbers of the files which need to be recovered by querying V$RECOVER_FILE and V$DATAFILE.
39
SELECT FILE#,ONLINE_STATUS,ERROR FROM V$RECOVER_FILE;
SELECT FILE#,NAME FROM V$DATAFILE;
SELECT FILE#,NAME FROM V$DATAFILE WHERE FILE#=1;
Step 3 Take the lost datafile offline so that the database can be opened: ALTER DATABASE DATAFILE 'E:\ORACLE\ORADATA\BACKUPS\SYSTEM01.DBF' OFFLINE; Step 4 Restore the lost datafile from the most recent backup. Say from Cold backup. COPY G:\Oracle_Backups\Backups\system01.dbf E:\ORACLE\ORADATA\BACKUPS\system01.dbf Step 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:
40
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." OR 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 ' E:\ORACLE\ORADATA\BACKUPS\system01.dbf '; Successful: Step 6 Bring the datafile, then the tablespace online after the file has been recovered: ALTER DATABASE DATAFILE 'E:\ORACLE\ORADATA\BACKUPS\system01.dbf' ONLINE; Step 7 Open the database. ALTER DATABASE OPEN; Step 8 Bring the SYSTEM tablespace online. ALTER TABLESPACE SYSTEM ONLINE; 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; Failure: ORA-01190 ORA-01110 Controlfiles are out of sync with datafiles.
41
When cold backup was done, trace of controlfile was done. ALTER DATABASE BACKUP CONTROLFILE TO TRACE; Delete control files BACKUPS_rebuild_controlfile_v2.sql. ------------------------------------------------------------------------------------------------------STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "BACKUPS" RESETLOGS ARCHIVELOG -- SET STANDBY TO MAXIMIZE PERFORMANCE MAXLOGFILES 5 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 1 MAXLOGHISTORY 226 LOGFILE GROUP 1 'E:\ORACLE\ORADATA\BACKUPS\REDO01.LOG' SIZE 100M, GROUP 2 'E:\ORACLE\ORADATA\BACKUPS\REDO02.LOG' SIZE 100M, GROUP 3 'E:\ORACLE\ORADATA\BACKUPS\REDO03.LOG' SIZE 100M -- STANDBY LOGFILE DATAFILE 'E:\ORACLE\ORADATA\BACKUPS\SYSTEM01.DBF', 'E:\ORACLE\ORADATA\BACKUPS\UNDOTBS01.DBF', 'E:\ORACLE\ORADATA\BACKUPS\INDX01.DBF', 'E:\ORACLE\ORADATA\BACKUPS\TOOLS01.DBF', 'E:\ORACLE\ORADATA\BACKUPS\NEWMEDIA\USERS01.DBF' CHARACTER SET WE8ISO8859P1 ; ------------------------------------------------------------------------------------------------------ORA-01189 ORA-01110 What we have is system tablespace is out of syc with the rest of tablespaces in the control files. Point being you can’t mix or match ! You need a backup of tablespace that is quite recent or cold backup of the entire database. Restore from previous full backup.
42
#------------------------Scenario 17 - Missing Data File - non-SYSTEM tablespace file #------------------------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. Step 1 Full Backup rman Target SYS/BACKUPS@BACKUPS catalog Rmancat/Rmancat@Rmancat Backup database plus archivelog; Backup archivelog all; Step 2 set ORACLE_SID=BACKUPS sqlplus /nolog CONN SYS/Backups AS SYSDBA SHUTDOWN IMMEDIATE EXIT #Delete file ORA-01157 ORA-01110 Step 3 If intentional: ALTER DATABASE DATAFILE 'E:\ORACLE\ORADATA\BACKUPS\UNDOTBS01.DBF' OFFLINE DROP; If not, offline: ALTER DATABASE DATAFILE 'E:\ORACLE\ORADATA\BACKUPS\UNDOTBS01.DBF' OFFLINE; ALTER DATABASE OPEN; Step 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.
43
DROP TABLESPACE USERS INCLUDING CONTENTS CASCADE CONSTRAINTS; #------------------------Scenario 18 - Loss of Non-Essential Datafile When Database is Down #------------------------Situation: This scenario assumes that the datafile for the INDX tablespace has been deleted. Step 1 Full Backup rman Target SYS/BACKUPS@BACKUPS catalog Rmancat/Rmancat@Rmancat Backup database plus archivelog; Backup archivelog all; Step 2 set ORACLE_SID=BACKUPS sqlplus /nolog CONN SYS/Backups AS SYSDBA SHUTDOWN IMMEDIATE EXIT #Delete file Step 3 Does DBA have scripts to rebuild indexes manually ? Yes: Take datafile offline and open database Rebuild datafile and run scripts No: INDX datafile should be restored and recovered as with any other datafile Go to step 4 Step 4 Startup open; ORA-01157 ORA-01110
44
Step 5 set ORACLE_SID=BACKUPS sqlplus /nolog CONN SYS/Backups AS SYSDBA SHUTDOWN IMMEDIATE; STARTUP MOUNT; Step 6 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;
SELECT FILE#,NAME FROM V$DATAFILE;
In this example, file #3 has been listed in V$RECOVER_FILE as needing recovery. To find out the path/name of file #3, query V$DATAFILE: SELECT FILE#,NAME FROM V$DATAFILE WHERE FILE#=3; The result is: E:\ORACLE\ORADATA\BACKUPS\INDX01.DBF
45
Step 7 Take the lost datafile offline so that the database can be opened: ALTER DATABASE DATAFILE 'E:\ORACLE\ORADATA\BACKUPS\INDX01.DBF' OFFLINE; ALTER DATABASE OPEN; 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. Step 8 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; Step 9 Re-create the INDX tablespace using the same datafile filename using the reuse option. CREATE TABLESPACE INDX DATAFILE 'E:\ORACLE\ORADATA\BACKUPS\INDX01.DBF' SIZE 50M REUSE AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL AUTOALLOCATE LOGGING ONLINE; Step 10 Re-build all of the indexes with the DBA's index rebuild scripts.
46
#------------------------Scenario 19 - Recover a Lost Datafile with No Backup with RMAN with Catalog #------------------------Situation: The USERS 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. Step 1 Full Backup rman Target SYS/BACKUPS@BACKUPS catalog Rmancat/Rmancat@Rmancat Backup database plus archivelog; Backup archivelog all; Step 2 SELECT FILE#,NAME FROM V$DATAFILE;
Select tablespace_name, extent_management, contents, segment_space_management from dba_tablespaces;
Select * from dba_data_files;
47
Add datafile ALTER TABLESPACE USERS ADD DATAFILE ‘E:\ORACLE\ORADATA\BACKUPS\NEWMEDIA\USERS02.DBF' SIZE 205M; Step 3 set ORACLE_SID=BACKUPS sqlplus /nolog CONN SYS/Backups AS SYSDBA SHUTDOWN IMMEDIATE EXIT #Delete file Step 4 Startup open; ORA-01157 ORA-01110 Step 5 Determine the file# and original path/filename for each file in the database which will need to be restored and recovered. SELECT FILE#,NAME FROM V$DATAFILE;
For this example, datafile 6 has been lost, which is used for the USERS tablespace. The original file path is: E:\ORACLE\ORADATA\BACKUPS\NEWMEDIA\USERS02.DBF
48
Step 6 set ORACLE_SID=BACKUPS sqlplus /nolog CONN SYS/Backups AS SYSDBA SHUTDOWN IMMEDIATE STARTUP MOUNT; ALTER DATABASE DATAFILE 'E:\ORACLE\ORADATA\BACKUPS\NEWMEDIA\USERS02.DBF' OFFLINE; ALTER DATABASE CREATE DATAFILE 'E:\ORACLE\ORADATA\BACKUPS\NEWMEDIA\USERS02.DBF'; ALTER DATABASE DATAFILE 'E:\ORACLE\ORADATA\BACKUPS\NEWMEDIA\USERS02.DBF' ONLINE; Step 7 The data contained within the missing 2nd datafile will then be reconstructed from the archivelog files data during the RMAN recovery process. rman Target SYS/BACKUPS@BACKUPS catalog Rmancat/Rmancat@Rmancat RUN { ALLOCATE CHANNEL ch3 TYPE Disk; RECOVER TABLESPACE USERS; ALTER DATABASE OPEN; RELEASE CHANNEL ch3; } QUIT The database is now available for production use. Full Backup
49
#------------------------Scenario 20 - Recover a Lost Datafile with No Backup without RMAN #------------------------As Scenario 19 – steps 3,4,5 Next, Step 6 (modified) set ORACLE_SID=BACKUPS sqlplus /nolog CONN SYS/Backups AS SYSDBA SHUTDOWN IMMEDIATE STARTUP MOUNT; ALTER DATABASE DATAFILE 'E:\ORACLE\ORADATA\BACKUPS\NEWMEDIA\USERS02.DBF' OFFLINE; ALTER DATABASE CREATE DATAFILE 'E:\ORACLE\ORADATA\BACKUPS\NEWMEDIA\USERS02.DBF'; ALTER DATABASE DATAFILE 'E:\ORACLE\ORADATA\BACKUPS\NEWMEDIA\USERS02.DBF' ONLINE; RECOVER TABLESPACE USERS; ALTER DATABASE OPEN; Successful: Failure: ORA-00279: change 671807 generated at 10/23/2009 15:45:12 needed for thread 1 ORA-00289: suggestion : G:\ORADATA\RECOVERY_AREA\BACKUPS\ARCHIVELOGS\ARC00021_2 073096330.001 ORA-00280: change 671807 for thread 1 is in sequence #21 Specify log: {=suggested | filename | AUTO | CANCEL} ALTER DATABASE OPEN; ORA-00308: cannot open archived log 'ALTER' ORA-27041: unable to open file OSD-04002: unable to open file O/S-Error: (OS 2) The system cannot find the file specified. Enter AUTO ALTER DATABASE OPEN; Then successful
50
#------------------------Scenario 21 - Recover a Lost Datafile with No Backup with RMAN with Catalog with CommVault #------------------------Situation: The USERS tablespace contains 3 datafiles. The database has been backed up, but the 3rd datafile was added after the most recent database backup. Therefore there is no backup of the 3rd datafile. Step 1 Full Backup using CommVault Step 2 SELECT FILE#,NAME FROM V$DATAFILE;
Select tablespace_name, extent_management, contents, segment_space_management from dba_tablespaces;
Select * from dba_data_files;
51
Add datafile ALTER TABLESPACE USERS ADD DATAFILE ‘E:\ORACLE\ORADATA\BACKUPS\NEWMEDIA\USERS03.DBF' SIZE 205M; Step 3 set ORACLE_SID=BACKUPS sqlplus /nolog CONN SYS/Backups AS SYSDBA SHUTDOWN IMMEDIATE EXIT #Delete file Step 4 Startup open; Maybe get errors: ORA-01157 ORA-01110 Step 5 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;
Step 6 set ORACLE_SID=BACKUPS sqlplus /nolog CONN SYS/Backups AS SYSDBA SHUTDOWN IMMEDIATE STARTUP MOUNT; Step 7 Recover users tablespace
52
53
54
Script Preview run { allocate channel ch1 type 'sbt_tape' PARMS="BLKSIZE=0"; recover tablespace 'USERS' ; } Generates error, so try again RMAN-06094: datafile 7 must be restored
run { allocate channel ch1 type 'sbt_tape' PARMS="BLKSIZE=0"; restore tablespace 'USERS' ; recover tablespace 'USERS' ; }
55
Generates Error RMAN-06023
56
#------------------------Scenario 22 - Recover from User Errors - dropped table or deleted data with RMAN with Catalog #------------------------Step 1 Full Backup rman Target SYS/BACKUPS@BACKUPS catalog Rmancat/Rmancat@Rmancat Backup database plus archivelog; Backup archivelog all; Step 2 Create table t (i number) / select count(*) from t; Zilth begin for i in 1..150 loop insert into t values (i); end loop; end; commit / select count(*) from t / 150 Select to_char(sysdate,'DD-MM-YYYY HH24:MI:SS') from dual / 30-10-2009 14:56:58 Step 3 delete from t where i between 1 and 75 / commit / 75
57
select count(*) from t / 25 Step 4 (restore of database + archive logs on top) rman Target SYS/BACKUPS@BACKUPS catalog Rmancat/Rmancat@Rmancat run { shutdown immediate; startup mount; set until time "to_date('30-10-2009 14:56:58','DD-MM-YYYY HH24:MI:SS')"; recover database; } Errors RMAN-03002 RMAN-06555 Yours datafiles are newer than 30-10-2009 14:56:58 which contains the committed data ,for incomplete recovery how will you apply redo?? To perform incomplete media recovery, you must restore all datafiles from backups created prior to the time to which you want to recover. Try again rman Target SYS/BACKUPS@BACKUPS catalog Rmancat/Rmancat@Rmancat run { shutdown immediate; startup mount; set until time "to_date('30-10-2009 14:56:58','DD-MM-YYYY HH24:MI:SS')"; restore database; recover database; alter database open resetlogs; } select count(*) from t / 150
58
#------------------------Scenario 23 - Recover from User Errors - dropped table or deleted data with RMAN with NO Catalog #------------------------Step 1 Full Backup rman Target SYS/BACKUPS@BACKUPS catalog Rmancat/Rmancat@Rmancat Backup database plus archivelog; Backup archivelog all; Step 2 Create table tt (i number) / select count(*) from tt; Zilth begin for i in 1..150 loop insert into tt values (i); end loop; end; commit / select count(*) from tt / 150 Select to_char(sysdate,'DD-MM-YYYY HH24:MI:SS') from dual / 02-11-2009 10:59:03 Step 3 delete from t where i between 1 and 75 / commit / 59
75 Step 4 (restore of database + archive logs on top) rman Target SYS/BACKUPS@BACKUPS nocatalog run { shutdown immediate; startup mount; set until time "to_date('02-11-2009 10:59:03','DD-MM-YYYY HH24:MI:SS')"; recover database; } Errors RMAN-03002 RMAN-06555 Yours datafiles are newer than 02-11-2009 10:59:03 which contains the committed data ,for incomplete recovery how will you apply redo?? To perform incomplete media recovery, you must restore all datafiles from backups created prior to the time to which you want to recover. Try again rman Target SYS/BACKUPS@BACKUPS nocatalog run { shutdown immediate; startup mount; set until time "to_date('02-11-2009 10:59:03','DD-MM-YYYY HH24:MI:SS')"; restore database; recover database; alter database open resetlogs; } select count(*) from tt / 150 #------------------------Scenario 24 - Recover from User Errors - dropped table or deleted data 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.
60
#------------------------Scenario 25 - Recover from User Errors - dropped table or deleted data – Flashback Drop #------------------------We should remember the thing is you must use automatic undo management to use the Flashback Table feature. It is based on undo information stored in an undo tablespace. Details A recycle bin contains all the dropped database objects until, - You permanently drop them with the PURGE command. - Recover the dropped objects with the UNDROP command. - There is no room in the tablespace for new rows or updates to existing rows. - The tablespace needs to be extended. You can view the dropped objects in the recycle bin from two dictionary views: - user_recyclebin - lists all dropped user objects - dba_recyclebin - lists all dropped system-wide objects The recycle Bin is a logical structure within each tablespace that holds dropped tables and objects related to the tables, such as indexes. The space associated with the dropped table is not immediately available but shows up in the dictionary view dba_free_space. When space pressure occurs in the tablespace, objects in the recycle bin are deleted in a FIFO fashion. The dropped objects still belongs to the owner and still counts against the quota for the owner in the tablespace. flashback table orders to before drop; If after drop table orders we already created, the dropped table can be recovered by new name. flashback table orders to before drop rename to orders_droped; Recycle Bin Considerations and Limitations: Few limitations are: 1. Only non-system locally managed tablespaces can have a recycle bin. However, dependent objects in a dictionary-managed tablespace are protected if the dropped object is in a locally managed tablespace. A table’s dependent objects are saved in the recycle bin when the table is dropped, except for bitmap join indexes, referential integrity constraints
61
Important Things: 1) objects will go to recyclebin or it will not go is based on recyclebin parameter settings. If I set alter system set recyclebin=off then object will not go in recycle bin. 2)Dropped SYS and SYSTEM schema objects are don't go in recyclebin. 3)The un-drop feature brings the table back to its original name, but not the associated objects like indexes and triggers, which are left with the recycled names.Views and procedures defined on the table are not recompiled and remain in the invalid state. These old names must be retrieved manually and then applied to the flashed-back table. Step 1 - Set-up first To be able to flashback on you have to use enterprise edition **** Restore Point Prerequisites: •
COMPATIBLE initialization parameter must be > 10.2
SELECT * FROM V$VERSION; Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Prod SELECT * FROM V$OPTION; Flashback Table Flashback Database
TRUE TRUE
If not enabled, you get ORA-00439 shutdown immediate; startup mount; alter database archivelog; alter system set DB_FLASHBACK_RETENTION_TARGET=4320; alter system set DB_RECOVERY_FILE_DEST_SIZE=536870912; alter system set DB_RECOVERY_FILE_DEST= 'E:\Recovery_Area\Dev\Flashback'; alter database flashback on; alter database open; show parameter DB_FLASHBACK_RETENTION_TARGET; show parameter DB_RECOVERY_FILE_DEST_SIZE; show parameter DB_RECOVERY_FILE_DEST;
62
We've defined a flasback retension of 4320 minutes (or 72 hours), a recovery file size of 512MB and defined the location for the file recovery area Step 1 (user – non SYS) Create table create table test (col_a varchar(4)); select object_name, original_name, type from user_recyclebin; Ziltch drop table test; select object_name, original_name, type from user_recyclebin;
Step 2 Create table again create table test (col_b varchar(4)); Drop table test;
Step 3 Restoring table
63
flashback table “BIN$DQ2wRd/iSGyLVbAsOfaqiQ==$0” to before drop; Step 4 (Dropping a Table Permanently) drop table test purge; Step 5 (Purging the User Recycle Bin) purge recyclebin; Step 6 (as above, SYS) purge dba_recyclebin; #------------------------Scenario 26 - Recover from User Errors - dropped table or deleted data – Flashback Table and Query #------------------------Flashback Table allows you to recover a table or tables to a specific point in time without restoring a backup. When you use the Flashback Table feature to restore a table to a specific point in time, all associated objects, such as indexes, constraints, and triggers will be restored. Flashback Table operations are not valid for the following object types: - Tables that are part of a cluster - Materialized views - Advanced Queuing tables - Static data dictionary tables - System tables - Partitions of a table - Remote tables (via database link) Flashback Table is extremely useful when a user accidentally inserts, deletes, or updates the wrong rows in a table. It provides a way for users to easily and quickly recover a table to a previous point in time. However, if the following DDL commands are issued, the flashback table command does not work: - ALTER TABLE ... DROP COLUMN - ALTER TABLE ... DROP PARTITION - CREATE CLUSTER - TRUNCATE TABLE - ALTER TABLE ... MOVE
64
Plus Flashback table works in-place by rolling back only the changes made to the table or tables and their dependent objects such as indexes. Flashback table is different from Flashback drop; Flashback Table undoes recent transactions to an existing table, whereas Flashback Drop recovers a dropped table. Flashback Table use data in the undo tablespace, whereas Flashback Drop uses the recycle bin. TO be able to flashback a table, you must enable row movement for the table as the ROWID in the table change. Steps to Flashback emp and dept tables to 15 mins before position. alter table emp enable row movement; alter table dept enable row movement; flashback table emp,dept To timestamp systimestamp – interval ‘15’ minute; You can return the Flashback Table command with a timestamp or SCN as long as the undo data is still available. Integrity constraints are not violated when one or more tables are Flashback. Flashback Query allows a user to “go back in time“ and view the contents of a table as it was existed at some point in the recent past. Before users can take advantage of the Flashback Query features, the DBA must perform two tasks: 1. Make sure that there is an undo tablespace in the database that is large enough to retain changes made by all users for a specified period of time. 2. Specify how long the undo information will be retained for user by flashback queries by using the initialization parameter undo_retention. This parameter is specified in seconds. See example below. Step 1 - CREATE UNDO TABLESPACE Create UNDO Tablespace UNDOTBS01 Datafile 'E:\oracle\product\10.1.0\oradata\Dev\UNDOTBS01.DBF' size 50m autoextend on next 10m; Step 2 SET THREE PARAMETERS UNDO_MANAGEMENT UNDO_TABLESPACE UNDO_RETENTION show parameters undo;
65
NAME TYPE VALUE ------------------------------------ ----------- ------undo_management string MANUAL undo_retention integer 900 undo_tablespace string show parameter spfile; create pfile from spfile; shutdown immediate; Update parameter file undo_management='AUTO' undo_retention=1200 undo_tablespace='UNDOTBS01' Startup Database based on modified pfile Create spfile from modified pfile Shutdown immediate Startup open show parameters undo; NAME TYPE VALUE ------------------------------------ ----------- --------undo_management string AUTO undo_retention integer 1200 undo_tablespace string UNDOTBS01 Step 3 Check undo guarantee Select tablespace_name, retention FROM dba_tablespaces where contents ='UNDO'; TABLESPACE_NAME RETENTION ------------------------------ ----------UNDOTBS01 NOGUARANTEE ALTER TABLESPACE UNDOTBS1 RETENTION GUARANTEE; Step 4 Size Undo Tablespace -->Worse case, length of task. Select max(undoblks) from v$undostat; MAX(UNDOBLKS) ------------77 (Test Database just created, no activity)
66
FORMULA SIZE UNDO TABLESPACE = (Result / 10 minutes (length) / 60 Seconds) * 8192 * 1200 (77/10/60) * 8192 * 1200 = 1261568 = 1M Step 4 – Example 1 Example (assumes the user has correct permissions !!!) Select * from Scott.emp;
Delete from Scott.emp where empno < 7900; 11 rows deleted. RollBack; Select * from Scott.emp;
Select * from Scott.emp as of timestamp (systimestamp - interval '10' minute) minus Select * from Scott.emp;
67
Insert into Scott.emp Select * from Scott.emp as of timestamp (systimestamp - interval '10' minute) minus Select * from Scott.emp;
Flashback Row History Flashback Query was first introduced in Oracle9i to provide a way to view historical data. In Oracle 10g, this feature has been extended. You can now retrieve all versions of the rows that exist or ever existed between the time the query was issued and a point back in time. This type of query is called Flashback Row History. You can use the VERSIONS BETWEEN clauses to retrieve all historical data related to a row. Let's take a look at the example below: Step 5 – Example 2 create table emp (name varchar2(10), salary number(8,2)); insert into emp 68
values ('DANIEL',2000); commit; update emp set salary = 3000 where name = 'DANIEL'; commit; select * from emp; Step 6 select * from emp versions between scn minvalue and maxvalue; NAME SALARY ---------- ---------DANIEL 3000 DANIEL 2000 As you can see, the Flashback Row History feature retrieves all committed occurrences of the row. It provides you with a way to view and repair historical data. In addition, it also provides a new way to audit the rows of a table and retrieve information about the transactions that changed the rows. You can use the transaction ID obtained from Flashback Row History to perform transaction mining using LogMiner or Flashback Transaction History (see next section) to obtain additional information about the transaction. The VERSION BETWEEN clause does not change the query plan. You can use the clause in a SELECT statement against a view. However, you cannot use the VERSION BETWEEN clause in a view definition. The row history data is stored in the undo tablespace. The undo_retention initialization parameter specifies how long the database will keep the committed undo information. If a new transaction needs to use undo space and there is not enough free space left, any undo information older than the specified undo retention period will be overwritten. Therefore, you may not be able to see all the row histories. However, you can set the undo tablespace option to RETENTION GUARANTEE to retain all row histories. To verify the retention value for the tablespace, you can issue the following statement: select tablespace_name, retention From dba_tablespaces;
69
Step 7 The Flashback Transaction History feature provides a way to view changes made to the database at the transaction level. It allows you to diagnose problems in your database and perform analysis and audit transactions. You can use this feature in conjunction with the Flash Row History feature to roll back the changes made by a transaction. You can also use this feature to audit user and application transactions. The Flashback Transaction History provides a faster way to undo a transaction than LogMiner. You can retrieve the transaction history from flashback_transaction_query view: Desc flashback_transaction_query;
select * from emp versions between scn minvalue and maxvalue;
select * from dba_transaction_query
70
where xid = 'xxxxxx'; Step 8 – Example 3 (Flashback To SCN) FLASHBACK TABLE TO SCN [ TRIGGERS] CREATE TABLE t ENABLE ROW MOVEMENT AS SELECT owner, table_name, tablespace_name FROM all_tables WHERE 1=2; desc t SELECT table_name, row_movement FROM user_tables; SELECT current_scn, SYSTIMESTAMP FROM v$database; -- 5607529 02-MAY-07 12.46.50.906000 PM -07:00 INSERT INTO t SELECT owner, table_name, tablespace_name FROM all_tables WHERE owner = 'SYS'; COMMIT; SELECT current_scn, SYSTIMESTAMP FROM v$database; -- 5607537 02-MAY-07 12.47.06.453000 PM -07:00 INSERT INTO t SELECT owner, table_name, tablespace_name FROM all_tables WHERE owner = 'WMSYS'; COMMIT; SELECT current_scn, SYSTIMESTAMP FROM v$database; -- 5607545 02-MAY-07 12.47.25.359000 PM -07:00 INSERT INTO t SELECT owner, table_name, tablespace_name FROM all_tables WHERE owner = 'CTXSYS'; COMMIT;
71
SELECT current_scn, SYSTIMESTAMP FROM v$database; -- 5607552 02-MAY-07 12.47.38.187000 PM -07:00 SELECT owner, COUNT(*) FROM t GROUP BY owner; conn / as sysdba set linesize 121 col owner format a10 FLASHBACK TABLE Ackers.t TO SCN 5607547; SELECT owner, COUNT(*) FROM t GROUP BY owner; FLASHBACK TABLE Ackers.t TO SCN 5607540; SELECT owner, COUNT(*) FROM t GROUP BY owner; Step 9 – Example 4 (Flashback to Timestamp) FLASHBACK TABLE TO TIMESTAMP [ TRIGGERS] CREATE TABLE t ENABLE ROW MOVEMENT AS SELECT owner, table_name, tablespace_name FROM all_tables WHERE 1=2; desc t SELECT table_name, row_movement FROM user_tables; SELECT current_scn, SYSTIMESTAMP FROM v$database; -- 5607702 02-MAY-07 12.51.33.390000 PM -07:00 INSERT INTO t SELECT owner, table_name, tablespace_name FROM all_tables WHERE owner = 'SYS'; COMMIT;
72
SELECT current_scn, SYSTIMESTAMP FROM v$database; -- 5607709 02-MAY-07 12.51.46.187000 PM -07:00 INSERT INTO t SELECT owner, table_name, tablespace_name FROM all_tables WHERE owner = 'WMSYS'; COMMIT; SELECT current_scn, SYSTIMESTAMP FROM v$database; -- 5607716 02-MAY-07 12.52.00.562000 PM -07:00 INSERT INTO t SELECT owner, table_name, tablespace_name FROM all_tables WHERE owner = 'CTXSYS'; COMMIT; SELECT current_scn, SYSTIMESTAMP FROM v$database; -- 5607722 02-MAY-07 12.52.13.359000 PM -07:00 SELECT owner, COUNT(*) FROM t GROUP BY owner; conn / as sysdba set linesize 121 col owner format a10 FLASHBACK TABLE Ackers.t TO TIMESTAMP TO_TIMESTAMP('02-MAY-07 12.51.52.050000'); SELECT owner, COUNT(*) FROM t GROUP BY owner; FLASHBACK TABLE Ackers.t TO TIMESTAMP TO_TIMESTAMP('02-MAY-07 12.51.51.500000'); SELECT owner, COUNT(*) FROM t GROUP BY owner; Step 10 – Example 5 (Flashback to Restore point)
73
FLASHBACK TABLE TO RESTORE POINT [ TRIGGERS] CREATE TABLE t ENABLE ROW MOVEMENT AS SELECT owner, table_name, tablespace_name FROM all_tables WHERE 1=2; desc t SELECT table_name, row_movement FROM user_tables; CREATE RESTORE POINT zero; INSERT INTO t SELECT owner, table_name, tablespace_name FROM all_tables WHERE owner = 'SYS'; COMMIT; CREATE RESTORE POINT one; INSERT INTO t SELECT owner, table_name, tablespace_name FROM all_tables WHERE owner = 'WMSYS'; COMMIT; CREATE RESTORE POINT two; INSERT INTO t SELECT owner, table_name, tablespace_name FROM all_tables WHERE owner = 'CTXSYS'; COMMIT; SELECT owner, COUNT(*) FROM t GROUP BY owner; SELECT scn, time, name FROM gv$restore_point; FLASHBACK TABLE t TO RESTORE POINT two; SELECT owner, COUNT(*) FROM t 74
GROUP BY owner; FLASHBACK TABLE t TO RESTORE POINT one; SELECT owner, COUNT(*) FROM t GROUP BY owner; FLASHBACK TABLE t TO RESTORE POINT zero; SELECT owner, COUNT(*) FROM t GROUP BY owner; #------------------------Scenario 27 - Recover from User Errors - dropped table or deleted data – Using Log Miner #------------------------LogMiner is used to view past activity in the database. LogMiner extracts all DDL and DML activity from the redo log files for viewing via the dynamic performance view v$logmnr_contents. The v$logmnr_contents view also contains the DML statements needed to retrieve the changes made to the database. The LogMiner not actually undo the changes, it only provides the statement that you can use to undo the change. #------------------------Scenario 28 - Recover from User Errors - dropped table or deleted data – Flashback Database #------------------------Also Restore Point Prerequisites: •
COMPATIBLE initialization parameter must be > 10.2
Flashback Database Prerequisites: • •
Database must be configured for and running in ARCHIVELOG mode Database must have configured and be using a FLASH_RECOVERY_AREA
lashback Database Demo An alternative strategy to the demo presented here is to use Recovery Manager
RMAN> FLASHBACK DATABASE TO SCN = ;
75
Dependent Objects
GV_$FLASHBACK_DATABASE_LOG V_$FLASHBACK_DATABASE_LOG GV_$FLASHBACK_DATABASE_LOGFILE V_$FLASHBACK_DATABASE_LOGFILE GV_$FLASHBACK_DATABASE_STAT V_$FLASHBACK_DATABASE_STAT
Syntax 1: SCN
FLASHBACK [STANDBY] DATABASE [] TO [BEFORE] SCN
Syntax 2: TIMESTAMP
FLASHBACK [STANDBY] DATABASE [] TO [BEFORE] TIMESTMP
Syntax 3: RESTORE POINT
FLASHBACK [STANDBY] DATABASE [] TO [BEFORE] RESTORE POINT
Flashback Syntax Elements ALTER DATABASE FLASHBACK OFF OFF
ON
alter database flashback off; ALTER DATABASE FLASHBACK ON alter database flashback on;
ALTER SYSTEM SET db_flashback_retention_target = Set Retention ; Target
alter system set DB_FLASHBACK_RETENTION_TARGET = 2880;
Start flashback on a tablespace
ALTER TABLESPACE FLASHBACK ON;
Stop flashback on a tablespace
ALTER TABLESPACE FLASHBACK OFF;
alter tablespace example flashback on;
alter tablespace example flashback off;
Initialization Parameters Setting the location of the flashback recovery area
db_recovery_file_dest=/oracle/flash_recovery_area
Setting the size of the flashback recovery area
db_recovery_file_dest_size=2147483648
Setting the retention time for flashback files (in minutes)
-- 2 days db_flashback_retention_target=2880
76
SELECT flashback_on, log_mode FROM v$database; set linesize 121 col name format a30 col value format a30 SELECT name, value FROM gv$parameter WHERE name LIKE '%flashback%';
As SYS
As ACKERS
SELECT current_scn FROM v$database; SELECT oldest_flashback_scn, oldest_flashback_time FROM gv$flashback_database_log; GRANT flashback any table TO Ackers; create table t ( mycol VARCHAR2(20)) ROWDEPENDENCIES; INSERT INTO t VALUES ('ABC'); INSERT INTO t VALUES ('DEF'); COMMIT; CREATE RESTORE POINT before_damage; INSERT INTO t VALUES ('GHI'); 77
COMMIT; SELECT ora_rowscn, mycol FROM t;
SHUTDOWN immediate; startup mount exclusive; -- be sure to substitute your SCN FLASHBACK DATABASE TO SCN 19513917; or FLASHBACK DATABASE TO RESTORE POINT bef_damage; /* FLASHBACK DATABASE TO TIMESTAMP (SYSDATE-1/24); FLASHBACK DATABASE TO TIMESTAMP timestamp'2002-1105 14:00:00'; FLASHBACK DATABASE TO TIMESTAMP to_timestamp('2002-11-11 16:00:00', 'YYYY-MM-DD HH24:MI:SS'); */ -- this will fail alter database open; -- this will succeed alter database open resetlogs; conn Ackers/Ackers SELECT ora_rowscn, mycol FROM t;
78
SELECT * FROM gv$flashback_database_stat; alter system switch logfile; shutdown immediate; startup mount exclusive; alter database flashback off; alter database noarchivelog; alter database open; SELECT flashback_on, log_mode FROM v$database; host rman target sys/pwd@orabase RMAN> crosscheck archivelog all; RMAN> delete archivelog all; RMAN> list archivelog all;
Examples: Using Restore Points (Configuration for Examples) To create the test user and test table used in the following examples, use the following SQL/PL SQL to create the user and insert 200 rows of sample data (code below assumes you have a USERS tablespace): --Create user and grant necessary permissions create user testuser identified by testuser; grant create table to testuser; grant create session to testuser; grant create procedure to testuser; grant execute on dbms_lock to testuser; alter user testuser quota 5m on users; --Connect as the newly created testuser and create the test table and logging table conn testuser/testuser; 79
create table testuser.test (id int, name varchar2(5)) tablespace users; create table testuser.logging (timestamp date, loginfo varchar2(15)) tablespace users; --Insert sample data into the test table declare i number(5); begin for i in 1..200 loop insert into testuser.test values (i, 'a'||i); end loop; commit; end; / In this simple example, we'll use a normal restore point to flash back a table to our testing starting point after it has been modified during the testing of a new procedure. For simplicity's sake, the procedure will only delete rows from our table, but it could just as easily have been performing complicated business logic. Because we have flashback logging disabled, it isn't possible for us to perform a flashback database (we'll receive a 'ORA-38726: Flashback database logging is not on.' if we attempt it). Because we'll be using flashback table, we need to enable row movement for the 'testuser.test' table involved, in effect giving Oracle permission to modify the ROWIDs for the rows in our table when it moved things around during the flashback table. SQL> alter table testuser.test enable row movement; Table altered. Create the 'process_data' procedure, representative of a new batch process we're testing: SQL> create or replace procedure testuser.process_data is 2 begin 3 delete from testuser.test where id > 50; 4 commit; 5 end; 6/ Procedure created. Next, we'll create a normal restore point 'before_test1' to mark our starting point for testing. This will allow us to return to this point in time to make changes and retest our new batch process without having to note the SCN or guess at the timestamp:
80
SQL> create restore point before_test1; Restore point created. Next, we'll execute the "process_data" procedure. After the procedure has completed, we see that the data has been processed correctly, and only 50 rows remain in the testuser.test table: SQL> exec testuser.process_data; PL/SQL procedure successfully completed. SQL> select count(*) from testuser.test; COUNT(*) ---------50
We've completed testing for the first iteration of our batch process. To allow testing of the procedure to run against the testuser.test table once again, we'll use our restore point to flash back the table to our starting point, and begin the subsequent rounds of testing:
SQL> flashback table testuser.test to restore point before_test1; Flashback complete. Once we've completed the testing and our restore point is no longer needed, we're free to drop the restore point from the database. Note that this step isn't actually required, as the restore points will automatically age out of the database, but it's probably a good idea to keep things as tidy as possible. We'll drop the restore point to minimize confusion: SQL> drop restore point before_test1; Restore point dropped. illustrate the use of a guaranteed restore point, we'll use an example where we have a new batch process we're testing that will be moving to production in the near future, and would like to test multiple algorithms to determine the most efficient method for processing the data. We'll create a restore point before running the tests, enabling us to in effect "reset" the database back to the exact same point in time before each test. This will make our testing much easier, ensuring that we're doing an applesto-apples comparison of the algorithms while minimizing both the amount of 81
work the DBA must do to restore the old data as well as the amount of time spent to do this restore. Because we'd like to have the ability to utilize the full spectrum of functionality provided by restore points and flashback database, we'll enable flashback database now. Ensure the prerequisites have been met (both Restore Point Prerequisites and Flashback Database Prerequisites above), and use the following steps to enable flashback logging on your database: oracle @dbatest:~> sqlplus / as sysdba SQL*Plus: Release 10.2.0.2.0 - Production on Tue May 27 10:18:42 2008 Copyright (c) 1982, 2005, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production With the Partitioning, OLAP and Data Mining options SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 159383552 bytes Fixed Size 1259696 bytes Variable Size 62916432 bytes Database Buffers 92274688 bytes Redo Buffers 2932736 bytes Database mounted. SQL> alter database flashback on; Database altered. SQL> alter database open; Database altered. After we've enabled flashback database, we'll create a second test procedure to "process" our data for this example. As in example 1, our procedure will make some basic manipulations on our data, which will represent a more complex business process. This time, however, our procedure will affect more than one table, allowing us to illustrate the effect of the more global flashback database (vs. the flashback table from example 1). SQL> create or replace procedure testuser.process_data is 2 begin
82
3 insert into testuser.logging values (sysdate, 'Begin procedure'); 4 delete from testuser.test where id > 50; 5 insert into testuser.logging values (sysdate, 'End procedure'); 6 commit; 7 end; 8/ Procedure created. With our batch processing procedure compiled, we're ready to create a guaranteed restore point to "label" the current starting point for use later in flashing back the database: SQL> create restore point before_test guarantee flashback database; Restore point created.
As we've now tagged our current point in time, we'll execute the batch processing procedure, and verify that we have only 50 rows remaining in the testuser.test table, and that we've generated some logging in our testuser.logging table: SQL> exec testuser.process_data(5); PL/SQL procedure successfully completed. SQL> select count(*) from testuser.test; COUNT(*) ---------50 SQL> select * from testuser.logging; TIMESTAMP LOGINFO --------- --------------27-MAY-08 Begin procedure 27-MAY-08 End procedure now that we've executed the first test of our new batch process, we need to "reset" the data back to how it appeared before running the test. We'll shut down and remount the database, then use the ''flashback database" command to "reset" the database to our specified restore point: SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. 83
SQL> startup mount; ORACLE instance started. Total System Global Area 159383552 bytes Fixed Size 1259696 bytes Variable Size 62916432 bytes Database Buffers 92274688 bytes Redo Buffers 2932736 bytes Database mounted. SQL> flashback database to restore point before_test; Flashback complete. We've now "reset" the database back to the restore point. We need to re-open the database, but because we're essentially doing a point-in-time recovery, we need to use the "resetlogs" command to recreate the redo logs and create a new instantiation of the database:
SQL> alter database open resetlogs; Database altered. We can verify that we once again see 200 rows in the "testuser.test" table, that our "testuser.logging" table is empty, and that we're ready to test another iteration of our batch processing algorithm: SQL> select count(*) from testuser.test; COUNT(*) ---------200 SQL> select count(*) from testuser.logging; COUNT(*) ---------0 Once we've completed our several iterations of testing, we're ready to remove the guaranteed restore point and free up whatever space the database used to manage the restore point. We accomplish this with the following: SQL> drop restore point before_test; Restore point dropped. Conclusion
84
Restore points provide a means for a DBA to label a particular point-in-time in the timeline of the database with a user-defined name, without digging into the details of SCNs or timestamps. The guaranteed restore point takes this a step further, providing the ability to guarantee a flashback database is possible until the DBA decides it is no longer needed, along with the ability to perform a flashback database without enabling flashback logging (with a reduced level of functionality). In conclusion, restore points make an invaluable addition to the DBA's arsenal of tools, useful any time a point-in-time recovery is needed.
#------------------------Scenario 29 - Loss of an Online Undo Segment Datafile (Open or Closed Database) with RMAN with Catalog #------------------------Step 1 Full Backup rman Target SYS/BACKUPS@BACKUPS catalog Rmancat/Rmancat@Rmancat Backup database plus archivelog; Backup archivelog all; Step 2 SELECT FILE#,NAME FROM V$DATAFILE;
FILE #2 E:\ORACLE\ORADATA\BACKUPS\UNDOTBS01.DBF
85
Step 3 set ORACLE_SID=BACKUPS sqlplus /nolog CONN SYS/Backups AS SYSDBA SHUTDOWN IMMEDIATE EXIT #Delete file Step 4 Startup open; ORA-01157 ORA-01110
Step 5 rman Target SYS/BACKUPS@BACKUPS catalog Rmancat/Rmancat@Rmancat RUN { STARTUP MOUNT; ALLOCATE CHANNEL ch3 TYPE Disk; RESTORE datafile 2; RECOVER datafile 2; ALTER DATABASE OPEN; RELEASE CHANNEL ch3; } QUIT Full Backup #------------------------Scenario 30 - Loss of an Online Undo Segment Datafile (Open or Closed Database) with RMAN with NO Catalog #------------------------Step 1 Full Backup rman Target SYS/BACKUPS@BACKUPS catalog Rmancat/Rmancat@Rmancat Backup database plus archivelog; Backup archivelog all;
86
Step 2 SELECT FILE#,NAME FROM V$DATAFILE;
FILE #2 E:\ORACLE\ORADATA\BACKUPS\UNDOTBS01.DBF
Step 3 set ORACLE_SID=BACKUPS sqlplus /nolog CONN SYS/Backups AS SYSDBA SHUTDOWN IMMEDIATE EXIT #Delete file Step 4 Startup open; ORA-01157 ORA-01110 Step 5 rman Target SYS/BACKUPS@BACKUPS nocatalog RUN { STARTUP MOUNT; ALLOCATE CHANNEL ch3 TYPE Disk; RESTORE datafile 2; RECOVER datafile 2; ALTER DATABASE OPEN; RELEASE CHANNEL ch3; } QUIT
87
Full Backup #------------------------Scenario 31 - Loss of an Online Undo Segment Datafile (Open or Closed Database) without RMAN #------------------------Step 1 Full Backup rman Target SYS/BACKUPS@BACKUPS catalog Rmancat/Rmancat@Rmancat Backup database plus archivelog; Backup archivelog all; Step 2 SELECT FILE#,NAME FROM V$DATAFILE;
FILE #2 E:\ORACLE\ORADATA\BACKUPS\UNDOTBS01.DBF Step 3 set ORACLE_SID=BACKUPS sqlplus /nolog CONN SYS/Backups AS SYSDBA SHUTDOWN IMMEDIATE EXIT #Delete file Step 4 Startup open; ORA-01157 ORA-01110 Step 5
88
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;
To find out the path/name of file #2, query V$DATAFILE: SELECT FILE#,NAME FROM V$DATAFILE WHERE FILE#=2; The result is: E:\ORACLE\ORADATA\BACKUPS\UNDOTBS01.DBF Step 6 Take the lost datafile offline so that the database can be opened: ALTER DATABASE DATAFILE 'E:\ORACLE\ORADATA\BACKUPS\UNDOTBS01.DBF' OFFLINE; Step 6 Restore the lost datafile from the most recent backup (WHATEVER) host COPY c:\backup\prod3_undo01.dbf c:\u01\prod3\undo01.dbf Step 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 undotbs1; 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 'E:\ORACLE\ORADATA\BACKUPS\UNDOTBS01.DBF'; Step 8 Bring the datafile, then the tablespace online after the file has been recovered:
89
ALTER DATABASE DATAFILE 'E:\ORACLE\ORADATA\BACKUPS\UNDOTBS01.DBF' ONLINE; Step 9 Open the database. ALTER DATABASE OPEN; Step 10 Bring the undotbs tablespace online. ALTER TABLESPACE undotbs1 ONLINE; Step 11 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; Full Backup #------------------------Scenario 32 - Failure during Open Database - tablespace in backup mode with RMAN with Catalog #------------------------Step 1 Full Backup rman Target SYS/BACKUPS@BACKUPS catalog Rmancat/Rmancat@Rmancat Backup database plus archivelog; Backup archivelog all; Step 2 SELECT TABLESPACE_NAME, FILE_NAME FROM SYS.DBA_DATA_FILES WHERE TABLESPACE_NAME = 'USERS';
90
Step 3 Mark the beginning of the online tablespace backup. For example, the following statement marks the start of an online backup for the tablespace users: ALTER TABLESPACE users BEGIN BACKUP; Step 4 You have forgotten you have placed TABLESPACE users in backup mode, much later. You need to do backup or whatever Shutdown immediate; Errors ORA-01149 ORA-01110 Step 5 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 #5,6,7 are still in backup mode (because the file status is "ACTIVE"). Step 6 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; Or Alternately you may issue commands to take just the tablespace or datafile out of backup mode:
91
ALTER TABLESPACE USERS END BACKUP; ALTER DATABASE DATAFILE 'c:\u01\prod3\users01.dbf' END BACKUP; Step 7 ALTER DATABASE OPEN; Step 8 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; no rows selected SELECT * FROM V$BACKUP;
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. Full Backup rman Target SYS/BACKUPS@BACKUPS catalog Rmancat/Rmancat@Rmancat
92
Backup database plus archivelog; Backup archivelog all;
#------------------------Scenario 33 - Create new logfile groups and drop old groups #------------------------Step 1 rman Target SYS/BACKUPS@BACKUPS catalog Rmancat/Rmancat@Rmancat Backup database plus archivelog; Backup archivelog all; Step 2 We have 3 groups with 1 member each SELECT GROUP#, STATUS, MEMBER FROM V$LOGFILE;
SELECT * FROM V$LOG;
93
Step 3 Kill existing, shutdown and delete/rename ORA-00313 ORA-00312 Step 4 Query the V$LOGFILE view to determine which logfile members are invalid: SELECT GROUP#,STATUS,MEMBER FROM V$LOGFILE;
create 3 more online redo logfile groups, containing 2 mirrored datafiles in each group. ALTER DATABASE ADD LOGFILE GROUP 4 ('E:\ORACLE\ORADATA\BACKUPS\REDO04_1.LOG', 'E:\ORACLE\ORADATA\BACKUPS\REDO04_2.LOG') SIZE 10M; ALTER DATABASE ADD LOGFILE GROUP 5 ('E:\ORACLE\ORADATA\BACKUPS\REDO05_1.LOG', 'E:\ORACLE\ORADATA\BACKUPS\REDO05_2.LOG') SIZE 10M; ALTER DATABASE ADD LOGFILE GROUP 6 ('E:\ORACLE\ORADATA\BACKUPS\REDO06_1.LOG', 'E:\ORACLE\ORADATA\BACKUPS\REDO06_2.LOG') SIZE 10M; Get Error ORA-01185 Group 6 Also check parameter 1) MAXLOGFILES 2) MAXLOGMEMBERS
-
5 3
alter database backup controlfile to trace; So, you cannot create group 6 Drop groups
94
ALTER DATABASE DROP LOGFILE GROUP 1; ALTER DATABASE DROP LOGFILE GROUP 2; ALTER DATABASE DROP LOGFILE GROUP 3; Group 2 in use, bring back Group 2 Switch the current logfile group until none of the original logfile groups (1,3) are being used. ALTER SYSTEM SWITCH LOGFILE; ALTER SYSTEM SWITCH LOGFILE; ALTER SYSTEM SWITCH LOGFILE; SELECT GROUP#,STATUS,MEMBER FROM V$LOGFILE;
95
#------------------------Scenario 34 - Recover archive log using RMAN with Catalog #------------------------Steps 1 Find the gap When the DBA queries the V$ARCHIVE_GAP view and has a record returned, this indicates a gap in the archived redo logs as illustrated below and may require manual intervention by the DBA: select * from v$archive_gap; THREAD# LOW_SEQUENCE# HIGH_SEQUENCE# -------- -------------- -------------1 24 28 From the output above, the physical standby database is currently missing logs from sequence 24 to sequence 28 for thread 1. Note that this view only returns the next gap that is currently blocking managed recovery from continuing. After resolving the identified gap and starting managed recovery, the DBA should query the V$ARCHIVE_GAP view again on the physical standby database to determine the next (if any) gap sequence. This process should be repeated until there are no more gaps. Step 2 Where (***NEED TO USE VIEW v$archived_log ***) After identifying a gap (as shown above), the DBA will need to query the primary database to locate the archived redo logs on the primary database. The following query assumes the local archive destination on the primary database is LOG_ARCHIVE_DEST_1: SELECT name 96
FROM v$archived_log WHERE thread# = 1 AND dest_id = 1 AND sequence# BETWEEN 24 and 28; Step 3 (only if automatic setup been disabled) Copy the above redo log files to the physical standby database and register them using the ALTER DATABASE REGISTER LOGFILE or restore them e.g. SQL> ALTER DATABASE REGISTER LOGFILE /u02/oraarchive/TESTDB/arch_t1_s24.dbf';
Step 4 Restore a log file Say Restore archive logs between 1 and 8 List backup of archivelog sequence between 1 and 8; where
http://download.oracle.com/docs/cd/B19306_01/backup.102/b14194/rcmsynta 008.htm#sthref121
97
rman Target SYS/BACKUPS@BACKUPS catalog Rmancat/Rmancat@Rmancat run { ALLOCATE CHANNEL ch3 TYPE Disk; set archivelog destination to 'F:\oradata\Recovery_Area\Backups\Temp_Archivelogs'; restore archivelog sequence between 1 and 8; RELEASE CHANNEL ch3; } Or rman Target SYS/BACKUPS@BACKUPS catalog Rmancat/Rmancat@Rmancat run { ALLOCATE CHANNEL ch3 TYPE Disk; restore archivelog sequence between 1 and 8; RELEASE CHANNEL ch3; } Supplementary Information Its easier to understand this by example:Flush data from redo logs to archived logs ALTER SYSTEM SWITCH LOGFILE; --Point 1 - List of incarnation of database select * from v$database_incarnation; RESETLOGS_CHANGE# RESETLOGS_TIME PRIOR_RESETLOGS_CHANGE# PRIOR_RESETLOGS_TIME RESETLOGS_CHANGE# RESETLOGS_TIME PRIOR_RESETLOGS_CHANGE# PRIOR_RESETLOGS_TIME 922270 02-NOV-09
841924 30-OCT-09
--Point 2 - List current SCN SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL 1608517 --Point 3 - List of change for each redo log file
98
select a.group#, b.THREAD#, b.members , a.member logfile, SEQUENCE#, FIRST_CHANGE# from v$log b, v$logfile a where a.group#=b.group#; GROUP# THREAD# MEMBERS LOGFILE FIRST_CHANGE# 2 1 1546511
SEQUENCE#
1 E:\ORACLE\ORADATA\BACKUPS\REDO02.LOG
23
--Point 4 – details of redo log SELECT * FROM v$log GROUP# THREAD# SEQUENCE# FIRST_CHANGE# FIRST_TIME 2
1
23 104857600
BYTES MEMBERS ARCHIVED STATUS
1 YES
ACTIVE
1546511 24-NOV-09
SELECT * FROM v$logfile GROUP# STATUS TYPE MEMBER 2
ONLINE E:\ORACLE\ORADATA\BACKUPS\REDO02.LOG
--Point 5 – archive log destination Select DEST_ID,DEST_NAME,STATUS, ARCHIVER,DESTINATION, LOG_SEQUENCE from v$archive_dest;
--Point 6 – archive log details select * from v$archived_log WHERE SEQUENCE# =23 RECID STAMP NAME DEST_ID THREAD# SEQUENCE# RESETLOGS_CHANGE# RESETLOGS_TIME FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME BLOCKS BLOCK_SIZE CREATOR REGISTRAR STANDBY_DEST ARCHIVED APPLIED DELETED STATUS COMPLETION_TIME DICTIONARY_BEGIN
99
DICTIONARY_END END_OF_REDO BACKUP_COUNT ARCHIVAL_THREAD# ACTIVATION# 214 703959219 F:\ORADATA\RECOVERY_AREA\BACKUPS\ARCHIVELOGS\ARC00023_2073 096330.001 1 1 23 922270 02-NOV-09 1546511 24-NOV09 1608494 26-NOV-09 1816 512 ARCH ARCH NO YES NO NO A 26-NOV-09 NO NO NO 0 1 2141611485
922270 (# RESETLOGS_CHANGE# Point 1) 1 (DEST_ID Point 5) 23 (SEQUENCE# Point 4) 1546511 (FIRST_CHANGE# Point 4) (lowest SCN recorded in the log file) 1608494 (NEXT_CHANGE# (the highest SCN in the log file)) --Point 7 – In a disaster situation where all files are lost you can only recover to the last SCN in the archived redo logs SELECT ARCHIVELOG_CHANGE# FROM v$database; 1608494 To recover run { shutdown immediate; startup mount; # set until time 'Nov 15 2000 09:00:00'; # set until scn 1000;
# alternatively, you can specify SCN
# set until sequence 9923; # alternatively, you can specify log sequence number restore database; recover database; alter database open resetlogs;
100
} The system change number (SCN) is an ever-increasing value that uniquely identifies a committed version of the database at a point in time. Every time a user commits a transaction Oracle records a new SCN in redo logs. oracle uses SCNs in control files datafile headers and redo records. Every redo log file has both a log sequence number and low and high SCN. The low SCN records the lowest SCN recorded in the log file while the high SCN records the highest SCN in the log file. .
What is Database Incarnation ?
Database incarnation is effectively a new “version” of the database that happens when you reset the online redo logs using “alter database open resetlogs;”. -- log sequence number Oracle Database assigns each redo log file a new log sequence number every time a log switch occurs and LGWR begins writing to it. When the database archives redo log files, the archived log retains its log sequence number. A redo log file that is cycled back for use is given the next available log sequence number. Each online or archived redo log file is uniquely identified by its log sequence number. During crash, instance, or media recovery, the database properly applies redo log files in ascending order by using the log sequence number of the necessary archived and redo log files. --Views
101
Use this view to find out to which place archived redo logs are copied: v$archive_dest Select DEST_ID,DEST_NAME,STATUS, ARCHIVER,DESTINATION, LOG_SEQUENCE from v$archive_dest;
v$archive_dest_status This view allows to find status and errors for each of the defined SELECT DEST_ID, DEST_NAME, STATUS, DATABASE_MODE, RECOVERY_MODE,DESTINATION, ARCHIVED_SEQ# FROM v$archive_dest_status
v$archived_log Displays successfully archived redo logs. shows received logs on a primary standby database. SELECT * FROM v$archived_log
102
v$archive_gap Lists sequence numbers of the archived los that are known to be missing for each thread on a (physical?) standby database (highest gap only). SELECT * FROM v$archive_gap v$log_history Relationship between SCN and log files -each log file - has series change no's from first to next change... -based on archive log format
V$RECOVERY_LOG To locate archived log files, view V$ARCHIVED_LOG for all archives or V$RECOVERY_LOG for archives needed during recovery:
103
Select * from V$RECOVERY_LOG THREAD#, SEQUENCE#, TIME, ARCHIVE_NAME #------------------------Scenario 35 - Recover archive log using RMAN with Catalog with CommVault #------------------------Step 1 Full backup with CommVault. Step 2 Use the query SELECT * FROM v$archived_log ORDER BY SEQUENCE# desc
Delete archive logs – correspond to SEQUENCE# 34, 33 (any) RECID STAMP NAME DEST_ID THREAD# SEQUENCE# RESETLOGS_CHANGE# RESETLOGS_TIME FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME BLOCKS BLOCK_SIZE CREATOR REGISTRAR STANDBY_DEST ARCHIVED APPLIED DELETED STATUS COMPLETION_TIME DICTIONARY_BEGIN DICTIONARY_END END_OF_REDO BACKUP_COUNT ARCHIVAL_THREAD# ACTIVATION# 140 701543782 F:\ORADATA\RECOVERY_AREA\BACKUPS\ARCHIVELOGS\ARC00034_20 73096330.001 1 1 34 610126 20-OCT-09 816162 29-OCT-09 816171 29-OCT-09 1 512 FGRD FGRD NO YES NO NO A 29-OCT-09 NO NO NO 1 1 2140468449 These are archived Step 3 - In a disaster situation where all files are lost you can only recover to the last SCN in the archived redo logs SELECT ARCHIVELOG_CHANGE# FROM v$database;
104
Step 4 – Restore from CommVault archive logs
105
106
Script Preview run { allocate channel ch1 type 'sbt_tape' PARMS="BLKSIZE=0"; restore (archivelog from logseq 33 until logseq 34 thread = 1 ); } Error RMAN-20242: specification does not match any archive log in the recovery catalog Deleted logs from a previous database incarnation. So could not see the archive logs. Try again. Delete archive logs based on latest database incaranation Step 1
select * from v$database_incarnation;
RESETLOGS_CHANGE# RESETLOGS_TIME PRIOR_RESETLOGS_CHANGE# PRIOR_RESETLOGS_TIME 922270 02-NOV-09 841924 30-OCT-09
Step 2 SELECT * FROM v$archived_log WHERE RESETLOGS_CHANGE#=922270 ORDER BY SEQUENCE# desc Delete archive logs – correspond to SEQUENCE# 23, 22 (any) These are archived
107
RECID STAMP NAME DEST_ID THREAD# SEQUENCE# RESETLOGS_CHANGE# RESETLOGS_TIME FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME BLOCKS BLOCK_SIZE CREATOR REGISTRAR STANDBY_DEST ARCHIVED APPLIED DELETED STATUS COMPLETION_TIME DICTIONARY_BEGIN DICTIONARY_END END_OF_REDO BACKUP_COUNT ARCHIVAL_THREAD# ACTIVATION# 215 703959219 G:\ORADATA\RECOVERY_AREA\BACKUPS\ARCHIVELOGS\ARC00023_2 073096330.001 2 1 23 922270 02-NOV-09 1546511 24-NOV-09 1608494 26-NOV-09 1816 512 ARCH ARCH NO YES NO NO A 26-NOV-09 NO NO NO 2 1 2141611485 Step 3 Check --in a disaster situation where all files are lost you can only recover to the last SCN in the archived redo logs --Plus current log sequence
Step 4 Check rmancat - see if archive logs exist rman Target SYS/BACKUPS@BACKUPS catalog Rmancat/Rmancat@Rmancat
108
list backup of archivelog sequence between 22 and 23; List of Backup Sets =================== BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------36095 1K DISK 00:00:01 27-SEP-07 BP Key: 36098 Status: EXPIRED Tag: TAG20070927T152509 Piece Name: G:\ORADATA\RECOVERY_AREA\BACKUPS\RMAN_BACKUPS\ORA_DF63 440431 0_S75_S1 List of Archived Logs in backup set 36095 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------1 22 389331 27-SEP-07 389334 27-SEP-07 BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------36097 8K DISK 00:00:01 27-SEP-07 BP Key: 36100 Status: EXPIRED Tag: TAG20070927T152509 Piece Name: G:\ORADATA\RECOVERY_AREA\BACKUPS\RMAN_BACKUPS\ORA_DF63 440431 2_S76_S1 List of Archived Logs in backup set 36097 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------1 23 389334 27-SEP-07 389588 27-SEP-07 BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------36401 10K SBT_TAPE 00:00:13 28-SEP-07 BP Key: 36403 Status: AVAILABLE Tag: TAG20070928T113706 Piece Name: 2tit2mf2_1_1 List of Archived Logs in backup set 36401 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------1 22 389331 27-SEP-07 389334 27-SEP-07 1 23 389334 27-SEP-07 389588 27-SEP-07 BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------36402 10K SBT_TAPE 00:00:06 28-SEP-07 BP Key: 36404 Status: AVAILABLE Tag: TAG20070928T113706
109
Piece Name: 2uit2mfj_1_1 List of Archived Logs in backup set 36402 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------1 22 389331 27-SEP-07 389334 27-SEP-07 1 23 389334 27-SEP-07 389588 27-SEP-07 BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------58550 2M SBT_TAPE 00:00:11 17-JAN-08 BP Key: 58552 Status: EXPIRED Tag: TAG20080117T155740 Piece Name: gpj6d43k_1_1 List of Archived Logs in backup set 58550 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------1 22 3237653 11-JAN-08 3404201 17-JAN-08 BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------58551 2M SBT_TAPE 00:00:07 17-JAN-08 BP Key: 58553 Status: EXPIRED Tag: TAG20080117T155740 Piece Name: gqj6d444_1_1 List of Archived Logs in backup set 58551 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------1 22 3237653 11-JAN-08 3404201 17-JAN-08 BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------58768 282K SBT_TAPE 00:00:12 18-JAN-08 BP Key: 58770 Status: EXPIRED Tag: TAG20080118T084036 Piece Name: h0j6eus4_1_1 List of Archived Logs in backup set 58768 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------1 23 3404201 17-JAN-08 3424009 18-JAN-08 BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------58769 282K SBT_TAPE 00:00:07 18-JAN-08 BP Key: 58771 Status: EXPIRED Tag: TAG20080118T084036 Piece Name: h1j6eusk_1_1 List of Archived Logs in backup set 58769 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- ---------
110
1
23
3404201
17-JAN-08 3424009
18-JAN-08
BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------164252 402K SBT_TAPE 00:00:14 15-APR-08 BP Key: 164254 Status: UNAVAILABLE Tag: TAG20080415T215131 Piece Name: 30jdtmv3_1_1 List of Archived Logs in backup set 164252 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------1 22 1142143 14-APR-08 1170533 15-APR-08 BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------164253 402K SBT_TAPE 00:00:07 15-APR-08 BP Key: 164255 Status: UNAVAILABLE Tag: TAG20080415T215131 Piece Name: 31jdtmvk_1_1 List of Archived Logs in backup set 164253 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------1 22 1142143 14-APR-08 1170533 15-APR-08 BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------164453 408K SBT_TAPE 00:00:35 16-APR-08 BP Key: 164455 Status: UNAVAILABLE Tag: TAG20080416T214224 Piece Name: 35je0aq0_1_1 List of Archived Logs in backup set 164453 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------1 23 1170533 15-APR-08 1198720 16-APR-08 BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------164454 408K SBT_TAPE 00:00:32 16-APR-08 BP Key: 164456 Status: UNAVAILABLE Tag: TAG20080416T214224 Piece Name: 36je0ar4_1_1 List of Archived Logs in backup set 164454 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------1 23 1170533 15-APR-08 1198720 16-APR-08 BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------216252 512 SBT_TAPE 00:00:24 29-SEP-08 BP Key: 216253 Status: AVAILABLE Tag: TAG20080929T213457
111
Piece Name: 1fjrr9c1_1_1 List of Archived Logs in backup set 216252 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------1 22 769476 29-SEP-08 769480 29-SEP-08 BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------216804 409K SBT_TAPE 00:00:45 30-SEP-08 BP Key: 216805 Status: AVAILABLE Tag: TAG20080930T213900 Piece Name: 1jjrttvk_1_1 List of Archived Logs in backup set 216804 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------1 23 769480 29-SEP-08 797921 30-SEP-08 BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------340896 113K SBT_TAPE 00:00:23 07-OCT-09 BP Key: 340897 Status: AVAILABLE Tag: TAG20091007T215759 Piece Name: 16kr7tj7_1_1 List of Archived Logs in backup set 340896 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------1 22 520706 07-OCT-09 525459 07-OCT-09 1 23 525459 07-OCT-09 525463 07-OCT-09 BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------340942 112K DISK 00:00:01 08-OCT-09 BP Key: 340944 Status: AVAILABLE Tag: TAG20091008T120321 Piece Name: G:\ORADATA\RECOVERY_AREA\BACKUPS\RMAN_BACKUPS\ORA_DF69 971060 1_S40_S1 List of Archived Logs in backup set 340942 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------1 22 520706 07-OCT-09 525459 07-OCT-09 BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------340943 512 DISK 00:00:02 08-OCT-09 BP Key: 340945 Status: AVAILABLE Tag: TAG20091008T120321
112
Piece Name: G:\ORADATA\RECOVERY_AREA\BACKUPS\RMAN_BACKUPS\ORA_DF69 971060 1_S41_S1 List of Archived Logs in backup set 340943 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------1 23 525459 07-OCT-09 525463 07-OCT-09 BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------347781 1K DISK 00:00:01 23-OCT-09 BP Key: 347782 Status: AVAILABLE Tag: TAG20091023T160918 Piece Name: G:\ORADATA\RECOVERY_AREA\BACKUPS\RMAN_BACKUPS\ORA_DF70 102135 8_S120_S1 List of Archived Logs in backup set 347781 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------1 22 672283 23-OCT-09 672302 23-OCT-09 BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------347795 512 DISK 00:00:01 23-OCT-09 BP Key: 347796 Status: AVAILABLE Tag: TAG20091023T160924 Piece Name: G:\ORADATA\RECOVERY_AREA\BACKUPS\RMAN_BACKUPS\ORA_DF70 102136 4_S122_S1 List of Archived Logs in backup set 347795 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------1 23 672302 23-OCT-09 672310 23-OCT-09 BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------349164 2K SBT_TAPE 00:00:23 28-OCT-09 BP Key: 349165 Status: AVAILABLE Tag: TAG20091028T152953 Piece Name: 40ksuinh_1_1 List of Archived Logs in backup set 349164 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------1 22 672283 23-OCT-09 672302 23-OCT-09 1 23 672302 23-OCT-09 672310 23-OCT-09
113
BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------362826 909K SBT_TAPE 00:00:16 04-DEC-09 BP Key: 362827 Status: AVAILABLE Tag: TAG20091204T123016 Piece Name: 7dl02eep_1_1 List of Archived Logs in backup set 362826 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------1 22 1546508 24-NOV-09 1546511 24-NOV-09 1 23 1546511 24-NOV-09 1608494 26-NOV-09 We can get it…. Step 4 – Restore from CommVault archive logs
114
115
Script Preview run { allocate channel ch1 type 'sbt_tape' PARMS="BLKSIZE=0"; restore (archivelog from logseq 22 until logseq 23 thread = 1 ); } Done….
116
#------------------------Scenario 36 – Duplicate Database using RMAN with Catalog #------------------------Step 1 Full Backup rman Target SYS/BACKUPS@BACKUPS catalog Rmancat/Rmancat@Rmancat Backup database plus archivelog; Backup archivelog all; Step 2 First we must create a password file for the duplicate instance: Use Orapwd utlity orapwd file=E:\oracle\ora92\database\PWDDUP.ORA password=DUP entries=5 Step 3 Next add the appropriate entries into the tnsnames.ora and listener.ora files in the $ORACLE_HOME/network/admin directory. Remember to load the new configuration into the listener: # Added to the listener.ora (SID_DESC = (GLOBAL_DBNAME = DUP) (ORACLE_HOME = e:\oracle\ora92) (SID_NAME = DUP) ) # Added to the tnsnames.ora DUP = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = glasbs2)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = DUP) (INSTANCE_NAME = DUP) (GLOBAL_NAME =DUP) ) )
117
# Reload listener lsnrctl reload Step 4 Check connectivity Tnsping DUP Step 5 Create pfile from spfile of source database Rename initDUP.ora Modify plus add extra parameter (in red) initDUP.ora *.db_create_file_dest ='’e:\oracle\oradata\DUP' *.DB_FILE_NAME_CONVERT=('E:\oracle\oradata\BACKUPS\','E:\oracle\orada ta\DUP\') *.LOG_FILE_NAME_CONVERT=('E:\oracle\oradata\BACKUPS\','E:\oracle\orad ata\DUP\')
*.background_dump_dest='e:\oracle\admin\DUP\bdump' *.compatible='9.2.0.0.0' *.control_files='e:\oracle\oradata\DUP\control01.ctl','e:\oracle\oradata\DUP\co ntrol02.ctl','e:\oracle\oradata\DUP\control03.ctl' *.core_dump_dest='e:\oracle\admin\DUP\cdump' *.db_block_size=8192 *.db_cache_size=25165824 *.db_domain='' *.db_file_multiblock_read_count=16 *.db_name='DUP' *.fast_start_mttr_target=300 *.hash_join_enabled=TRUE *.instance_name='DUP' *.java_pool_size=0 *.large_pool_size=8388608 *.log_archive_dest_1='LOCATION=F:\oradata\Recovery_Area\DUP\Archivelo gs OPTIONAL REOPEN=300' *.log_archive_dest_2='LOCATION=g:\oradata\Recovery_Area\DUP\Archivelo gs OPTIONAL REOPEN=300' *.log_archive_format='ARC%S_%D.%T' *.log_archive_start=true *.open_cursors=300 *.pga_aggregate_target=25165824 *.processes=150 *.query_rewrite_enabled='FALSE' *.remote_login_passwordfile='EXCLUSIVE' *.shared_pool_size=50331648 *.sort_area_size=524288 118
*.star_transformation_enabled='FALSE' *.timed_statistics=TRUE *.undo_management='AUTO' *.undo_retention=15360000 *.undo_tablespace='UNDOTBS1' *.user_dump_dest='e:\oracle\admin\DUP\udump' Step 6
***********VERY IMPORTANT*************** Create directory structure for DUP Step 7 Create windows Service - Need to create service BEFORE create database ORADIM -NEW -SID DUP -STARTMODE AUTO -PFILE 'E:\oracle\ora92\database\INITDUP.ORA' Step 8 Next we start the database in NOMOUNT mode: STARTUP FORCE NOMOUNT; Step 9 With the duplicate database started we can now connect to it from RMAN. For the duplication to work we must connect to the original database (TARGET), the recovery catalog (CATALOG) and our duplicate database (DUP): rman Target SYS/BACKUPS@BACKUPS c atalog Rmancat/Rmancat@Rmancat AUXILIARY SYS/DUP@DUP Step 10 We can then dupicate the database using one of the following commands: # Duplicate database to TARGET's current state. DUPLICATE TARGET DATABASE TO DUP; # Duplicate database to TARGET's state 4 days ago. DUPLICATE TARGET DATABASE TO DUP UNTIL TIME 'SYSDATE-4';
119
Error RMAN-06054: media recovery requesting unknown log: thread 1 scn 1912736 run { ALTER DATABASE OPEN RESETLOGS; } Checked the archive log files there are there… Step 11 Check the database select INSTANCE_NAME,HOST_NAME,STARTUP_TIME,STATUS from v$instance; select DBID,NAME,CREATED,RESETLOGS_TIME,LOG_MODE, CONTROLFILE_CREATED from v$database select file_name from dba_data_files; #------------------------Scenario 37 – Duplicate Database using RMAN with Catalog with CommVault #------------------------Prerequisite Setup Steps for Creating a Duplicate Database
The following procedure describes the prerequisite setup steps for creating a duplicate database: Before You Begin: Review information on the Duplicate Database feature before beginning this procedure. •
Prerequisite setup steps: Prerequisite setup steps: Step 1 •
Run a full backup (along with the log files) on the original database.
Create a duplicate Oracle database instance. If using a different host, create the duplicate database instance on the destination host. •
120
Step 2 First we must create a password file for the duplicate instance: Use Orapwd utlity (specific password required) orapwd file=E:\oracle\ora92\database\PWDABC.ORA password= change_on_install entries=5 Step 3 Copy the init.ora file from the original database to the duplicate database instance. o
Create pfile from spfile; Update the database name and the database file locations in the init.ora file for the duplicate database instance. Also, add the DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT parameters in the init.ora file. The DB_FILE_NAME_CONVERT parameter is used to redirect the data files and temp files to a target location. Similarly, the LOG_FILE_NAME_CONVERT parameter can be used to redirect the archive log files to a target location. The syntax is as follows: o
DB_FILE_NAME_CONVERT=('SOURCE_OF_DF_PATH/','DUP_OF_ DF_PATH/','SOURCE_OF_TEMP_PATH/','DUP_OF_TEMP_PATH/',... ) LOG_FILE_NAME_CONVERT=('SOURCE_OF_LOG_PATH/REDO','D UP_OF_LOG_PATH/REDO') When using these parameters on a Windows computer, the file paths should be entered in uppercase. Make that all the other parameters in the init.ora file is same as that in the original database. In the case of table restore, provide the log_archive_dest_1 parameter in the init.ora file for the specified auxiliary instance. •
*.DB_FILE_NAME_CONVERT=('E:\oracle\oradata\BACKUPS\','E:\oracle\orad ata\ABC\') *.LOG_FILE_NAME_CONVERT=('E:\oracle\oradata\BACKUPS\','E:\oracle\or adata\ABC\')
121
Configure file *.background_dump_dest='e:\oracle\admin\ABC\bdump' *.compatible='9.2.0.0.0' *.control_files='e:\oracle\oradata\ABC\control01.ctl','e: \oracle\oradata\ABC\control02.ctl','e:\oracle\oradata\ABC \control03.ctl' *.core_dump_dest='e:\oracle\admin\ABC\cdump' *.db_block_size=8192 *.db_cache_size=25165824 *.db_domain='' *.db_file_multiblock_read_count=16 *.db_name='ABC' *.fast_start_mttr_target=300 *.hash_join_enabled=TRUE *.instance_name='ABC' *.java_pool_size=0 *.large_pool_size=8388608 *.log_archive_dest_1='LOCATION=F:\oradata\Recovery_Area\A BC\Archivelogs OPTIONAL REOPEN=300' *.log_archive_dest_2='LOCATION=g:\oradata\Recovery_Area\A BC\Archivelogs OPTIONAL REOPEN=300' *.log_archive_format='ARC%S_%D.%T' *.log_archive_start=true *.open_cursors=300 *.pga_aggregate_target=25165824 *.processes=150 *.query_rewrite_enabled='FALSE' *.remote_login_passwordfile='EXCLUSIVE' *.shared_pool_size=50331648 *.sort_area_size=524288 *.star_transformation_enabled='FALSE' *.timed_statistics=TRUE *.undo_management='AUTO' *.undo_retention=15360000 *.undo_tablespace='UNDOTBS1' *.user_dump_dest='e:\oracle\admin\ABC\udump' *.DB_FILE_NAME_CONVERT=('E:\oracle\oradata\BACKUPS\','E:\ oracle\oradata\ABC\') *.LOG_FILE_NAME_CONVERT=('E:\oracle\oradata\BACKUPS\','E: \oracle\oradata\ABC\')
122
Step 4
***********VERY IMPORTANT*************** Create directory structure for ABC Step 5 •
Create new Oracle Services if using a Windows computer.
Create windows Service - Need to create service BEFORE create database ORADIM -NEW -SID ABC -STARTMODE AUTO -PFILE 'E:\oracle\ora92\database\INITABC.ORA' Step 6 If using the same host, add the duplicate database instance name in the Listener.ora and Tnsnames.ora files. •
If using a different host, add the duplicate database instance name in the Listener.ora file on the destination host and Tnsnames.ora files on the destination and source hosts. Also, add the original database name in the Tnsnames.ora file on the destination host. •
# Added to the listener.ora (SID_DESC = (GLOBAL_DBNAME = ABC) (ORACLE_HOME = e:\oracle\ora92) (SID_NAME = ABC) ) # Added to the tnsnames.ora ABC = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = glasbs2)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = ABC) (INSTANCE_NAME = ABC) (GLOBAL_NAME = ABC) ) )
123
•
Restart the Listener.
lsnrctl reload Step 7 Check connectivity Tnsping ABC Step 8 •
Startup the duplicate database instance in the NOMOUNT mode.
Step 9 (create in CommVault new instance with ‘change_on_install’ hardcoded in) Provide a valid connect string for the auxiliary channel (used to duplicate the database). The system assumes and uses sys/change_on_install for the auxiliary channel. If using a different host, make sure that a valid connect string (sys/sys@) is provided in the Instance Properties (Details) tab instead of a '/'. •
With the duplicate database started we can now connect to it from RMAN. For the duplication to work we must connect to the original database (TARGET), the recovery catalog (CATALOG) and our duplicate database (ABC): rman Target SYS/BACKUPS@BACKUPS c atalog Rmancat/Rmancat@Rmancat AUXILIARY SYS/ABC@ABC See next section for details…. If using a different host, ensure that the Oracle iDataAgent is installed on the Destination host. •
If required, you can create a new Oracle instance for the duplicate database in the CommCell Console, open the instance properties and verify that the Oracle instance status of the duplicate database is STARTED. •
124
Create a Duplicate Database on the Same Host with the Instance Configured The following procedure describes the steps involved in creating a duplicate database on the same host with the instance configured. Review information on the Duplicate Database feature before beginning this procedure. •
Perform the Prerequisite Setup Steps for Creating a Duplicate Database. (see previous section) •
Required Capability: See Capabilities and Permitted Actions To create a duplicate database on the same host with the instance configured: 1. From the CommCell Console, right-click the source host instance then select All Tasks -> Restore. 2. From the Oracle Restore Options (General) dialog box, select the Duplicate DB checkbox then enter the Catalog Connect string and click Advanced. 3.
4. From the Duplicate tab in the Oracle Duplicate Options dialog box, configure the following fields:
125
Duplicate To - Select this option then enter the name for the duplicate database in the Database Name field. o
Pfile - For Oracle 10g and 9i, type in or Browse to the location of the Startup Pfile. o
Open Restricted - For Oracle 10g, you can select this option if you want to open the database in restricted mode. o
Log File - Select this option, then specify Group or File and click the appropriate Add button to enter specifications for the redo log group/file. o
5. From the Recover tab in the Advanced Restore Options dialog box, select the appropriate recover option.
126
6. Select any other desired options, then click Redirect in the Advanced Restore Options dialog box. 7. OK:
From the Redirect tab, choose one of the following then click
127
To redirect all tablespaces to the same location, select the Redirect All Table Spaces to: checkbox then type in or Browse to the new path. o
To redirect tablespaces/datafiles to different locations, select the Redirect checkbox then click the desired objects in the Object column to highlight them, and either type in or Browse to the new path. Click Apply. Repeat this process until all tablespaces/datafiles have been redirected. o
To redirect the database temp file, use the DB_FILE_NAME_CONVERT parameter in the initial file for the new database instance. Similarly, to redirect the log files, you can either use the LOG_FILE_NAME_CONVERT parameter in the initial file or use the Log File option in the Advanced Restore Options (Duplicate) dialog box. For
128
more information on setting the parameters in the initial file, see Prerequisite Setup Steps for Creating a Duplicate Database. 8. From the Duplicate DB Options tab in the Oracle Duplicate Options dialog box, configure the following options as desired:
o
Time Zone - Select a time zone for a point-in-time recovery.
No Filename Check - Select this option to prevent RMAN from performing a check for target datafiles sharing the same names as the duplicated files to see if they are in use. This option is required when you are using the same path (i.e., the standby and primary datafiles and logs have identical filenames). o
9.
Click OK on the Advanced Restore Options dialog box.
129
Script Preview ******DATA RESTORE SCRIPT****** run { set newname for datafile 'E:\ORACLE\ORADATA\BACKUPS\INDX01.DBF' to 'E:\oracle\oradata\ABC\INDX01.DBF'; set newname for datafile 'E:\ORACLE\ORADATA\BACKUPS\SYSTEM01.DBF' to 'E:\oracle\oradata\ABC\SYSTEM01.DBF'; set newname for datafile 'E:\ORACLE\ORADATA\BACKUPS\TOOLS01.DBF' to 'E:\oracle\oradata\ABC\TOOLS01.DBF'; set newname for datafile 'E:\ORACLE\ORADATA\BACKUPS\UNDOTBS01.DBF' to 'E:\oracle\oradata\ABC\UNDOTBS01.DBF'; set newname for datafile 'E:\ORACLE\ORADATA\BACKUPS\NEWMEDIA\USERS01.DBF' to 'E:\oracle\oradata\ABC\USERS01.DBF'; set newname for datafile 'E:\ORACLE\ORADATA\BACKUPS\NEWMEDIA\USERS02.DBF' to 'E:\oracle\oradata\ABC\USERS02.DBF'; set newname for datafile 'E:\ORACLE\ORADATA\BACKUPS\NEWMEDIA\USERS03.DBF' to 'E:\oracle\oradata\ABC\USERS03.DBF'; allocate auxiliary channel ch1 type 'sbt_tape' PARMS="BLKSIZE=262144,ENV=(CV_mmsApiVsn=2,CV_channelPar=ch1,T hreadCommandLine= RESTORE -jm 14 -a 2:0 -cl 73 -ins 65 -at 0 -j 0 -bal 0 -bap 0 -rap 0 -rcp 0 -mav 0 -ms 1 -p 2 -cn glasbs2 -vm Instance001)" TRACE 0; duplicate target database to ABC PFILE= E:\oracle\ora92\database\INITABC.ORA ; } exit;
130
Begin or schedule the restore. Once the job begins, if you see a pop-up message about the database state, ensure that the duplicate database is in NOMOUNT mode then click Yes to continue. Errors 1. RMAN-05500: the auxiliary database must be not mounted when issuing a DUPLICATE command It mounts the database in the job – need to shutdown and restart in nomount 2. RMAN-06054: media recovery requesting unknown log: thread 1 scn 2927472
Backups database select * from v$database_incarnation; RESETLOGS_CHANGE#
RESETLOGS_TIME PRIOR_RESETLOGS_CHANGE# PRIOR_RESETLOGS_TIME
922270
02-NOV-09
841924
30-OCT-09
SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL; 2932242 select a.group#, b.THREAD#, b.members , a.member logfile, SEQUENCE#, FIRST_CHANGE# from v$log b, v$logfile a where a.group#=b.group#;
131
SELECT * FROM v$log
SELECT ARCHIVELOG_CHANGE# FROM v$database; 2927472 select * from v$archived_log WHERE RESETLOGS_CHANGE#= 922270 AND SEQUENCE# =56 ORDER by SEQUENCE# DESC RECID STAMP NAME DEST_ID THREAD# SEQUENCE# RESETLOGS_CHANGE# RESETLOGS_TIME FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME BLOCKS BLOCK_SIZE CREATOR REGISTRAR STANDBY_DEST ARCHIVED APPLIED DELETED STATUS COMPLETION_TIME DICTIONARY_BEGIN DICTIONARY_END END_OF_REDO BACKUP_COUNT ARCHIVAL_THREAD# ACTIVATION# 282 708088571 F:\ORADATA\RECOVERY_AREA\BACKUPS\ARCHIVELOGS\ARC00056_2073 096330.001 1 1 56 922270 02-NOV-09 2927468 12-JAN-10 2927472 12-JAN-10 1 512 FGRD FGRD NO YES NO NO A 12-JAN-10 NO NO NO 1 1 2141611485 Basically select last archive log generated on the day of the backup. 10. Verify that the restore completed successfully and that the database is restored to the destination host. If you selected the Recover option verify that the database is consistent.
132
#------------------------Scenario 38 – Check and repair block corruption using RMAN with Catalog #------------------------ORA-01578 runtime error because there are one or more corrupt blocks How can corrupt blocks be caused? First of all we have two different kinds of block corruption: - physical corruption (media corrupt) - logical corruption (soft corrupt) Physical corruption can be caused by defected memory boards, controllers or broken sectors on a hard disk; Logical corruption can among other reasons be caused by an attempt to recover through a NOLOGGING action. There are two initialization parameters for dealing with block corruption: - DB_BOCK_CHECKSUM (calculates a checksum for each block before it is written to disk, every time) causes 1-2% performance overhead - DB_BLOCK_CHECKING (server process checks block for internal consistency after every DML) causes 1-10% performance overhead If performance is not a big issue then you should use these! Normally RMAN checks only for physically corrupt blocks with every backup it takes and every image copy it makes. RMAN doesn not automatically detect logical corruption by default! We have to tell it to do so by using CHECK LOGICAL! Step 1 Block corruption detected select last_name, salary from employees; ORA-01578: ORACLE data block corrupted (file # 5, block # 83) Step 2 Check alert log Wed Apr 5 08:17:40 2006 Hex dump of (file 5, block 83) in trace file /u01/app/oracle/admin/orcl/udump/orcl_ora_14669.trc
133
Corrupt block relative dba: 0×01400053 (file 5, block 83) Bad header found during buffer read Data in bad block: Step 3 Use RMAN to check a database for both physically and logically corrupt blocks. rman Target SYS/BACKUPS@BACKUPS catalog Rmancat/Rmancat@Rmancat backup validate check logical database; RMAN does not physically backup the database with this command but it reads all blocks and checks for corruptions. If it finds corrupted blocks it will place the information about the corruption into a view: Step 4 Check for block corruption select * from v$backup_corruption; select * from v$copy_corruption; select * from v$database_block_corruption; Step 5 Now we can tell RMAN to recover all the blocks which it has found as being corrupt: blockrecover corruption list;
134
Extra Notes -- Deciding Whether to Use RMAN with a Recovery Catalog -- Benefits of Using the Recovery Catalog as the RMAN Repository -- Costs of Using the Recovery Catalog as the RMAN Repository
Deciding Whether to Use RMAN with a Recovery Catalog By default, RMAN connects to the target database in NOCATALOG mode, meaning that it uses the control file in the target database as the sole repository of RMAN metadata. Perhaps the most important decision you make when using RMAN is whether to create a recovery catalog as the RMAN repository for normal production operations. A recovery catalog is a schema created in a separate database that contains metadata obtained from the target control file. In general, Oracle Corporation advises using a catalog when you manage multiple databases. If you have more than one database to back up, then you can create one systemwide recovery catalog and store metadata for all the databases in this catalog. Hence, you avoid the extra space requirements and memory overhead of maintaining multiple databases, each with a single catalog. You need to take extra precautions when backing up the catalog, however, because if you lose the catalog then you lose the metadata for multiple target databases.
Benefits of Using the Recovery Catalog as the RMAN Repository When you use a recovery catalog, RMAN can perform a wider variety of automated backup and recovery functions than when you use the control file in the target database as the sole repository of metadata. The following features are available only with a catalog: •
You can store metadata about multiple target databases in a single catalog.
•
You can store metadata about multiple incarnations of a single target database in the catalog. Hence, you can restore backups from any incarnation.
•
Resynchronizing the recovery catalog at intervals less than the CONTROL_FILE_RECORD_KEEP_TIME setting, you can keep historical metadata.
•
You can report the target database schema at a noncurrent time.
•
You can store RMAN scripts in the recovery catalog.
•
When restoring and recovering to a time when the database files that exist in the database are different from the files recorded in the mounted control file, the recovery catalog specifies which files that are needed. Without a catalog, you must first restore a control file backup that lists the correct set of database files.
135
•
If the control file is lost and must be restored from backup, and if persistent configurations have been made to automate the tape channel allocation, these configurations are still available when the database is not mounted.
Costs of Using the Recovery Catalog as the RMAN Repository The main cost of using a catalog is the maintenance overhead required for this additional database. For example, you have to: •
Find a database other than the target database to store the recovery catalog (otherwise, the benefits of maintaining the catalog are lost), or create a new database
•
Create enough space on the database for the RMAN metadata (as described in "Configuring the Recovery Catalog Database")
•
Back up the recovery catalog metadata
•
Upgrade the recovery catalog when necessary
Hence, unless you manage a network of databases, you may choose to avoid the overhead and use the control file as the exclusive repository of metadata. When you use a control file as the RMAN repository, RMAN still functions effectively. If you do not use a catalog, read the section "Managing the RMAN Repository Without a Recovery Catalog". Specifically, make sure you: •
Consider the costs of not using a recovery catalog, as described in "Understanding Catalog-Only Command Restrictions"
•
Develop a strategy for backing up the repository, as described in "Backing Up and Restoring the Control File"
136
