rman如何在備庫執行一致性備份

lhrbest發表於2020-09-24

rman如何在備庫執行一致性備份



先說下碰到的情況：

如果在dg的備庫進行rman備份，而拿不到最新的歸檔日誌，那麼這些備份很可能不能正常恢復一個新庫。因為，資料庫處於不一致的狀態，即使強制啟庫，也不可行。



Howto make a consistent RMAN backup in an Standby database in Active DataGuard mode (文件 ID 1419923.1)


In this Document

Goal
Solution
References

APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.2.0.1 and later
Enterprise Manager for Oracle Database - Version 12.1.0.2.0 to 12.1.0.2.0 [Release 12.1]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.

GOAL

Howto create a consistent RMAN backup on a Standby database in Active Dataguard Mode.

Consistent means, that the backup is having the datafiles and the related archived redologs, so that it can be opened with this backup. This done on the Primary database by using :

RMAN> backup database plus archivelog;


This will execute the following :

  1. Backup the existing archived redologs
  2. Backup the datafiles
  3. Switch a logfile
  4. Backup all the archives again, but this time it will include all the archived redologs created during and just after the datafile backup. This are the archives which are required to make the datafile backup consistent.

The goal of this document it to accomplish this in an Active DataGuard database.
An Active DataGuard database is a standby database which is open in READ ONLY, but with an ongoing recovery.
This ongoing recovery is updating the datablocks, even while the database is in READ ONLY mode.
From an RMAN perspective the datafiles will behave the same as in a Primary database, meaning that they are FUZZY and will need some recovery to become consistent.

As the standby database is open in READ ONLY, RMAN will NOT be able to force a switch. This is only done when a database is in READ WRITE. The archives are only created when the Primary is switching a logfile. So the above step(4) will NOT backup any new archived redologs and therefor this total backup will not be consistent as it requires additional archives which are not backed up (yet).

SOLUTION

The missing logswitch on the Active DataGuard database can be force, by making an explicit logswitch on the  Primary.

shell script : /usr/local/bin/logswitch.sh

#!/bin/ksh

#
# Change <passwd>
#        <primary_db> 
#

sqlplus -s "sys/<passwd>@<primary_db> as sysdba" <<EOF 
alter system archive log current;
exit
EOF

 

% chmod 755 /usr/local/bin/logswitch.sh


Example of RMAN backup script on the Active Dataguard Standby database

% rman target / catalog <un/pw@catalog_db>

Rman> Configure controlfile autobackup on  ;--------------> This would ensure Controlfile auto backup
RMAN> backup database plus archivelog delete input;

      host "/usr/local/bin/logswitch.sh";

      backup archivelog all delete input;

 

Additional Comments

Note 1: 
The missing 'Log Switch' by making an explicit 'Log Switch' on the Primary. 
also does apply to the BASIC DATA GUARD case as well where the database is MOUNTED and in 
managed recovery mode (MRP). 
A backup taken of a MOUNTED standby database in recovery mode will also not get all of the 
archived logs required unless a switch is performed on the primary and a second archived log backup is taken.

 

 

Note 2: 
For taking consistent RMAN Backups in a basic Standby 
"not" in Active DataGuard mode and not running recovery mode (MRP)
.
Please see:
 ( Doc ID 1292126.1) How to take consistent backups at standby site
.

 

Note 3:
The logswitch on primary is  NOT required in 11.2.0.4 and above anymore. 

Reference:
( Doc ID 1616074.1) RMAN-06820 ORA-17629 During Backup at Standby Site 

Bug 17580082 : ACTIVE STANDBY - RMAN-6820: WARNING: FAILED TO ARCHIVE CURRENT LOG AT PRIMARY
Status: 32 - Not a Bug

 

 

 

How to take consistent backups at standby site (文件 ID 1292126.1)


In this Document

Goal
Solution
References


APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.2.0.4 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.
***Checked for relevance on 08-Jan-2013***

GOAL

How to take an offline, consistent, self-contained backup at the standby site. The aim of this exercise is to have a self-contained backup that can be restored on its own without needing recovery.  I.e., since recovery of standby is stopped, the backup is a cold backup.  


This process will work for both RAC and non-RAC systems.

SOLUTION

1) stop managed recovery:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;


2) backup the database and controlfile

Please note :-
If you have a recovery catalog configured you can connect to it and take a backup.
Both the Primary and Standby database should use the same recovery catalog. Even though these database share the same DBID,Rman is able to differentiate the standby database from Primary .
Note you do not need to register the standby database in the catalog if the primary is already registered. Simply connect to the standby as target and then connect to recovery catalog for taking backup.



RMAN> backup database plus archivelog ;

RMAN> backup current controlfile; 
Note: In 10g, you will need to backup the controlfile from the primary site.

The above will result in a consistent, self-contained backup.


3) restart managed recovery:

 

To restore from this backup:

RMAN> startup nomount;
RMAN> restore controlfile from 'controlfile backuppiece name and location';
RMAN> alter database mount;
RMAN> restore database;
RMAN> recover database noredo;

 

 

For taking consistent RMAN Backups in Standby in  Active DataGuard mode
Please see:

 ( Doc ID 1419923.1) Howto make a consistent RMAN backup in an Standby database in Active DataGuard mode 
 

 

REFERENCES

NOTE:1419923.1  - Howto make a consistent RMAN backup in an Standby database in Active DataGuard mode

NOTE:735106.1  - How to Recreate a Controlfile



Step by Step method to create Primary/Standby Database from Standby Backup (文件 ID 1604251.1)

In this Document

Goal
Solution
References


APPLIES TO:

Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.

GOAL

Case (a): How to create  STANDALONE database from standby backup
Case (b): How to create  STANDBY database from standby backup

SOLUTION

NOTE: In the images and/or the document content below, the user information and environment data used represents fictitious data from the Oracle sample schema(s), Public Documentation delivered with an Oracle database product or other training material. Any similarity to actual environments, actual persons, living or dead, is purely coincidental and not intended in any manner.

For the purposes of this document, the following fictitious environment is used as an example to describe the procedure:

Database Name:     PRIM
First Standby Unique Name:  STBY_1
Second Standby Unique Name: STBY_2

******************

  Method of Restoring controlfile makes trick here. 

From 11gr2 onwards,

For 11.2.0.2 to 11.2.0.3

For case (a)   RMAN> restore controlfile from '<backuppiece>' 

           eg: RMAN> restore controlfile from 'E:\<path>\PRIM_34_12NEVQQH_1_1.BKP';

 

Note:

From 11.2.0.4 , behavior is changed due to fix of bug 7553431.


In 11.2.0.4, the following:

       RMAN> restore controlfile from '<backuppiece>'; 

will restore a controlfile with contolfile_type as "STANDBY" instead of "BACKUP".    The controlfile type can be found with:

       SQL>  select controlfile_type from v$database;

Due to controlfile_type=STANDBY, alter database open resetlogs will fail with error... at step (a4)

This issue is fixed with patch 18455956.   Once patch is applied, you should use below command to restore the controlfile as 'BACKUP':

       RMAN> restore PRIMARY controlfile from '<backuppiece>'

       

For case (b):   

          RMAN> restore standby controlfile from '<Backup piece > '; 
    eg: RMAN> restore standby controlfile from 'E:\<path>\PRIM_38_16NF0NI4_1_1.BKP';

  

Note:

Only from 11g, backup of controlfile taken with 'backup current controlfile' can be restored at standby database as:
     (a) current controlfile:  (restore controlfile from '<backuppiece>'; )
     (b) standby controlfile:  (restore standby controlfile from '<backuppiece>';) 

 

Example:  Case (a) Creating Primary/Normal database from Standby backup.

Sequence of steps: 

++ Assuming you have taken backup from standby database.  Now you need to create stand alone database using standby backup

Refer below notes for assistance :

How to take consistent backups at standby site (Doc ID  1292126.1)

How to make a consistent RMAN backup in an Standby database in Active DataGuard mode (Doc ID  1419923.1)

 

Step a1:  Create an init file with the following  minimum required parameters:

DB_NAME=PRIM                      # Database name of standby/primary
DB_UNIQUE_NAME="STBY_1"         # can be set for differentiation 
control_files='/<path>/<controlfile name>.CTL'
log_file_name_convert='<current standby REDO location>,<new host REDO location>' (#needed only if path is different )


Step a2:
       
$ export ORACLE_SID=STBY_1 
$ sqlplus / as sysdba
SQL> startup nomount;


Step a3:   In another session

$ export ORACLE_SID=STBY_1
$ rman target /

RMAN> restore controlfile from '<backup location>/<controlfile backuppiece>'; for ( case a)
RMAN> alter database mount;
RMAN> catalog start with '<backuppiece location>';
RMAN   run {
      allocate channel t1 type disk;
      allocate channel t2 type disk;
      set newname for datafile 1 to '<new location/datafile name>';
      set newname for datafile 2 to '<new location/datafile name>';
      ...
      set until sequence 212;  # this is to set as per your requirement            
      restore database;
      switch datafile all;
      recover database;
      }

Output:

 

RMAN> run {
2> allocate channel t1 type disk;
3> allocate channel t2 type disk;
4> set newname for datafile 1 to 'E:\ORADATA\STBY_1\DATA\SYSTEM01.DBF';
5> set newname for datafile 2 to 'E:\ORADATA\STBY_1\DATA\SYSAUX01.DB';
6> set newname for datafile 3 to 'E:\ORADATA\STBY_1\DATA\UNDOTBS01.DBF';
7> set newname for datafile 4 to 'E:\ORADATA\STBY_1\DATA\USERS01.DBF';
8> set until sequence 181;
9> restore database;
10> switch datafile all;
11> recover database;
12> }

released channel: ORA_DISK_1
allocated channel: t1
channel t1: SID=10 device type=DISK

allocated channel: t2
channel t2: SID=97 device type=DISK

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET until clause

Starting restore at 01-JUL-12

channel t1: starting datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
channel t1: restoring datafile 00001 to E:\ORADATA\STBY_1\DATA\SYSTEM01.DBF
channel t1: restoring datafile 00002 to E:\ORADATA\STBY_1\DATA\SYSAUX01.DB
channel t1: restoring datafile 00003 to E:\ORADATA\STBY_1\DATA\UNDOTBS01.DBF
channel t1: restoring datafile 00004 to E:\ORADATA\STBY_1\DATA\USERS01.DBF
channel t1: reading from backup piece E:\<path>\PRIM_31_0VNEVQM2_1_1.BKP
channel t1: piece handle=E:\<path>\PRIM_31_0VNEVQM2_1_1.BKP tag=TAG20120701T070538
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:01:05
Finished restore at 01-JUL-12

datafile 1 switched to datafile copy
input datafile copy RECID=19 STAMP=787476336 file name=E:\ORADATA\STBY_1\DATA\SYSTEM01.DBF
datafile 2 switched to datafile copy
input datafile copy RECID=20 STAMP=787476336 file name=E:\ORADATA\STBY_1\DATA\SYSAUX01.DB
datafile 3 switched to datafile copy
input datafile copy RECID=21 STAMP=787476336 file name=E:\ORADATA\STBY_1\DATA\UNDOTBS01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=22 STAMP=787476336 file name=E:\ORADATA\STBY_1\DATA\USERS01.DBF

Starting recover at 01-JUL-12

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 01-JUL-12
released channel: t1
released channel: t2

RMAN>

 


Step a4:  Open the database:

SQL>  alter database open resetlogs.

++  if log_file_name_convert is set at init , during database open, ORL will be created as per its value.

    example: Init.ora

    log_file_name_convert = 'D:\ORADATA\STBY_1\REDO\','E:\ORADATA\STBY_1\REDO\' 

else, at mount stage, you need to rename the online redo log location as below otherwise the 'alter database open resetlogs' will throw a 'file not found' error.

To do this, at mount stage:

SQL> alter database rename file '<old file location and name>' to '<new location and name>';
SQL> alter database open resetlogs;

 

  

For 11.2.0.4 :

Prior to applying the patch controlfile restore as 'STANDBY'

SQL> select controlfile_type from v$database;

     CONTROL
     -------
    STANDBY             <========

After applying the patch 18455956 controlfile restored as 'BACKUP'

Example in 11.2.0.4

Step a2:

$ export ORACLE_SID=aux
$ sqlplus / as sysdba
SQL> startup nomount;


Step a3:
In another session

$ export ORACLE_SID=aux

$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Mar 31 17:23:17 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PRIM (not mounted)

RMAN> restore primary controlfile from '/home/oracle/PRIM_176_5gr1kmhv_1_1.bkp';
RMAN> exit

In sql session

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 31 17:23:42 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> alter database mount;

Database altered.

SQL> select controlfile_type from v$database;

CONTROL
-------
BACKUP           <======

 

 

In 11.2.0.4 , you can follow below workaround if patch 18455956 NOT applied.

Since controlfile type would be 'STANDBY step (a4) will fail at 'Alter database open resetlogs'.   With any of below option you can open the database as primary role:

Option (a) Create trace file of controlfile
           SQL>  alter database backup controlfile to trace resetlogs;
           Use trace file generated by command to recreate the controlfile.
           SQL>  recover database using backup controlfile until cancel;   # Cancel the recovery
           SQL>  alter database open resetlogs;

Option (b) Activate the Standby

           SQL> Alter database activate physical standby database; 
           SQL> Alter database open;   

 

Note: Prior to activate need to clear standby logfile if we get error  ORA-00314

 

Example : Case (b) Creating standby database from standby backup;  

# This is not cascaded standby database. 
# Below example is to create another standby database from existing standby backup and to add  as second standby database to existing primary database
                                                                                     

   You need to change syntax on step (b3) of  case (a) to case (b)  like below

       RMAN> restore standby controlfile from '<backup location/<backuppiece containing controlfile>';

 

Step b1:  Create an init file with the following minimum required parameters:
      
DB_NAME=PRIM                               # Database name of standby/primary
DB_UNIQUE_NAME="STBY_2"                   # can be set for differentiation 
control_files='/<path>/<controlfile name>.CTL'
FAL_SERVER=<your existing primary service>
log_file_name_convert = '<current standby REDO location >,<new host REDO location>' (#needed only if path is different)

Step b2:

$ export ORACLE_SID=STBY_2
$ sqlplus / as sysdba
SQL> startup nomount;

Step b3:
In another session

$ export ORACLE_SID=STBY_2
$ rman target /

RMAN> restore standby controlfile from '<backup location/backuppiece with controlfile>';        #  Restoring controlfile as STANDBY type

RMAN> alter database mount;
RMAN> catalog start with '< backup piece location>';
RMAN>  run {
      allocate channel t1 type disk;
      allocate channel t2 type disk;
      set newname for datafile 1 to '<new location/datafile name>';
      set newname for datafile 2 to '<new location/datafile name>';
      ...
      set until sequence 212;  # this is to set as per your requirement            
      restore database;
      switch datafile all;
      recover database;
      }

Step b4:  Once new standby database is recovered, mount and  start MRP:

SQL> recover managed standby database disconnect;

Step b5:  Configure and enable redo shipping from existing primary to new standby database.  

 

 

NOTE : From 11.2 onward use RMAN DUPLICATE from ACTIVE DATABASE by connecting to standby as target to create standby.

 


 How to quickly check that Database is consistent after incomplete recovery (Point in Time Recovery) before OPEN RESETLOGS (文件 ID 1354256.1)


In this Document

Goal
Solution
Check 1: Checkpoint Time and Fuzziness
Check 2: Datafile Status
Check 3: Absolute Fuzzy
Check 4: Archive Logs Required

Check 5: Post OPEN RESETLOGS Activities
References

APPLIES TO:

Oracle Database - Enterprise Edition - Version 9.0.1.0 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.
***Checked for relevance on 05-Nov-2014***

GOAL

What is the LEAST amount of recovery that has to be done before a database restored from a backup can be opened?

After performing a RESTORE / RECOVER, we need to perform a quick validation to ensure database is consistent and ready for OPEN RESETLOGS.

This proactive check helps to prevent several issues which may appear during or after OPEN RESETLOGS.

Through out this note we assume that you are restoring from a valid backup.

There can be more scenarios than discussed here. Please consult Oracle Support when in doubt.

 

SOLUTION

   

NOTE: In the images and/or the document content below, the user information and environment data used represents fictitious data from the Oracle sample schema(s), Public Documentation delivered with an Oracle database product or other training material. Any similarity to actual environments, actual persons, living or dead, is purely coincidental and not intended in any manner.

For cold/offline backups, no archivelogs/recovery is necessary. You can simply open the database with resetlogs.

However for HOT/ONLINE backups, ALL archivelogs from backup start to backup end must be applied before the database can be opened - this is the MINIMUM amount of recovery needed. 

To determine which log was current at the time the backup completed, note the COMPLETION time of the database backup - take this from the backup log. 

If this is an RMAN backup you can also query the RMAN metadata. Ensure that the environment variable NLS_DATE_FORMAT is set before invoking rman so that timestamps as well as date are returned:

For unix:    

% export NLS_DATE_FORMAT='dd-mon-rr hh24:mi:ss'
% rman target /


For windows: 

> set nls_date_format=dd-mon-rr:hh24:mi:ss                
> rman target /


To find your backup:


RMAN> LIST BACKUP OF DATABASE COMPLETED AFTER '<date>';
 or 
RMAN> LIST BACKUP OF DATABASE COMPLETED AFTER 'sysdate-n';

Set <date> to limit the output to the backups that you are interested and note the completion time - for a multi-piece backup, note the completion time of the LAST backuppiece created.

 
Through out this note, when running SQL queries you should set NLS_DATE_FORMAT at the session level as follows:

SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS' ;

Check 1: Checkpoint Time and Fuzziness

Objective:  Verify that the datafiles are recovered to the intended point in time (PIT) and they are consistent (FUZZY=NO)

Query the current status and PIT ( Point  In  Time up to which the datafiles have been recovered) of datafiles by reading datafile headers directly from the physical datafiles:

SQL> select fuzzy, status, error, recover, checkpoint_change#, checkpoint_time, count(*) from v$datafile_header group by fuzzy, status, error, recover, checkpoint_change#, checkpoint_time ;

FUZ STATUS  ERROR           REC CHECKPOINT_CHANGE# CHECKPOINT_TIME        COUNT(*)
--- ------- --------------- --- ------------------ -------------------- ----------
NO  ONLINE                                 5311260 31-AUG-2011 23:10:14          6
YES ONLINE                                 5311260 31-AUG-2011 23:10:14          1



a) Verify that the checkpoint_time / checkpoint_change# is in line with your intended UNTIL TIME/SCN. If not, recover the database further if you have more archived logs available.

b) If FUZZY=YES for some datafiles, it means more recovery is required. If these archived logs are lost, identify such datafiles and determine if we can take them offline.  Warning: we will lose data in those datafiles if they are taken offline!

If the datafiles belong to SYSTEM or UNDO tablespace, we MUST not bring such datafile offline without proper analysis. Please consult Oracle Support for further actions.

SQL> select file#, substr(name, 1, 50), substr(tablespace_name, 1, 15), undo_opt_current_change# from v$datafile_header where fuzzy='YES' ;

     FILE# SUBSTR(NAME,1,50)                                  SUBSTR(TABLESPA UNDO_OPT_CURRENT_CHANGE#
---------- -------------------------------------------------- --------------- ------------------------
         3 /<path>/undotbs01.dbf                                   UNDOTBS1                  5117431


Occasionally, if the tablespace name doesn't indicate it to be UNDO tablespace, if we see non-zero value in column UNDO_OPT_CURRENT_CHANGE#, it indicates that the datafile contains undo segments.


To bring a datafile offline :

SQL> alter database datafile <file#> offline ;


Check 1 can be considered PASSED when :

a) Verified that all the datafiles are at the some checkpoint_time, and this is your intended Point in time.

b) Fuzzy=NO for SYSTEM, UNDO and all intended datafiles. For datafiles with Fuzzy=YES, either recover them further or bring them OFFLINE if no further archived logs are available.

Check 2: Datafile Status

Objective:  Verify that the files with status=RECOVER are not OFFLINE unintentionally

SQL> select status, enabled, count(*) from v$datafile group by status, enabled ;

STATUS  ENABLED      COUNT(*)
------- ---------- ----------
SYSTEM  DISABLED            1
ONLINE  READ WRITE          4
RECOVER DISABLED            2



If the files are in RECOVER status, verify if they are OFFLINE :

SQL> select file#, substr(name, 1, 50), status, error, recover from v$datafile_header ;



If you want the data for these files to be accessible, then bring them ONLINE :

SQL> alter database datafile <file#> ONLINE ;




Check 2 can be considered Passed when:

All the intended datafiles are not OFFLINE 

 

Check 3: Absolute Fuzzy

Objective:  Additional Fuzzy check (Absolute Fuzzy check) 


Occasionally, it is possible to see Fuzzy=NO and same checkpoint_change# for all the intended datafiles but OPEN RESETLOGS still fails

eg:

SQL> select fuzzy, status, error, recover, checkpoint_change#, checkpoint_time, count(*) from v$datafile_header group by fuzzy, status, error, recover, checkpoint_change#, checkpoint_time ;

FUZ STATUS  ERROR           REC CHECKPOINT_CHANGE#      CHECKPOINT_TIME   COUNT(*)
--- ------- --------------- --- ------------------ -------------------- ----------
NO  ONLINE                                 5311260 31-AUG-2011 23:10:14          7


SQL> ALTER DATABASE OPEN RESETLOGS ;

ORA-01194: file 4 needs more recovery to be consistent
ORA-01110: data file 3: '/<path>/undotbs02.dbf'



Hence, we should perform additional fuzzy check known as Absolute Fuzzy Check:

SQL> select hxfil file#, substr(hxfnm, 1, 50) name, fhscn checkpoint_change#, fhafs Absolute_Fuzzy_SCN, max(fhafs) over () Min_PIT_SCN from x$kcvfh where fhafs!=0 ;

FILE#      NAME                                               CHECKPOINT_CHANG ABSOLUTE_FUZZY_S     MIN_PIT_SCN
---------- -------------------------------------------------- ---------------- ---------------- ----------------
         4 /<path>/undotbs01.dbf                                  5311260          5311524          5311524
         6 /<path>/system01.dbf                                   5311260          5311379          5311524

 

Note: Column Min_PIT_SCN will return same value even for multiple rows as we have applied ANALYTICAL "MAX() OVER ()" function on it.


Above query indicates that the recovery must be performed at least UNTIL SCN 5311524 to make datafiles consistent and ready to OPEN. Since the checkpoint_change# is smaller than Min_PIT_SCN, the datafiles will ask for more recovery.

 

Check 3 can be considered PASSED when:

a) No rows selected from above query (i.e. Min_PIT_SCN is 0 (Zero) for all the datafiles)

b) Min_PIT_SCN is returned less than Checkpoint_Change#

 

Check 4: Archive Logs Required


Query the controlfile to find the latest archivelog required fore recovery. Lets say the backup completed at  31-AUG-2011 23:20:14:

SQL> -- V$ARCHIVED_LOG
SQL> --
SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-RR HH24:MI:SS';
SQL> SELECT THREAD#, SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG 
      WHERE '31-AUG-11 23:20:14' BETWEEN FIRST_TIME AND NEXT_TIME;


If the above query does not return any rows, it may be that the information has aged out of the controlfile - run the following query against v$log_history.

SQL> -- V$LOG_HISTORY  view does not have a column NEXT_TIME
SQL> --
SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-RR HH24:MI:SS';
SQL> select a.THREAD#, a.SEQUENCE#, a.FIRST_TIME 
       from V$LOG_HISTORY a 
      where FIRST_TIME = 
         ( SELECT MAX(b.FIRST_TIME) 
             FROM V$LOG_HISTORY b
            WHERE b.FIRST_TIME < to_date( '31-AUG-11 23:20:14',  'DD-MON-RR HH24:MI:SS') 
         ) ;
SQL>

 

The sequence# returned by the above query is the log sequence current at the time the backup ended - let say 530 thread 1. 

For minimum recovery use: (Sequence# as returned +1 )

RMAN> RUN 
{ 
 SET UNTIL SEQUENCE 531 THREAD 1;
 RECOVER DATABASE;
}

 

If this is a RAC implementation the use this SQL instead to query the controlfile:

SQL> SELECT THREAD#, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE# FROM V$ARCHIVED_LOG WHERE '31-AUG-11 23:20:14' BETWEEN FIRST_TIME AND NEXT_TIME;


For minimum recovery use the log sequence and thread that has the lowest NEXT_CHANGE# returned by the above query.

 

Check 4 can be considered PASSED when:

All archivelogs from the time of the backup to the end of the backup is available for use during recovery


Check 5: Post OPEN RESETLOGS Activities

During OPEN RESETLOGS, monitor the alert.log for additonal errors/messages. You might see some messages like below during dictionary check: 

Dictionary check beginning
Tablespace 'TEMP' #3 found in data dictionary, <(============================== (1)
but not in the controlfile. Adding to controlfile.
Tablespace 'USERS' #4 found in data dictionary, 
but not in the controlfile. Adding to controlfile.
File #4 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00004' in the controlfile. <(==================== (2)
File #5 is online, but is part of an offline tablespace. <(==================== (3)
data file 5: '/<path>/example01.dbf'
File #7 found in data dictionary but not in controlfile. <(==================== (2)
Creating OFFLINE file 'MISSING00007' in the controlfile.
File #8 is offline, but is part of an online tablespace. <(==================== (4)
data file 8: '/<path>/mydata02.dbf'
File #9 is online, but is part of an offline tablespace. <(==================== (3)
data file 9: '/<path>/example02.dbf'
Dictionary check complete


We discuss below the points highlighted :


(1) Check if the temp files exist. If not, add them as per your preference: 

SQL> select file#, name from v$tempfile ;

no rows selected

SQL> select file#, name from dba_temp_files ;

no rows selected


SQL> select tablespace_name, status, contents from dba_tablespaces where contents='TEMPORARY' ;

TABLESPACE_NAME                STATUS    CONTENTS
------------------------------ --------- ---------
TEMP                           ONLINE    TEMPORARY


SQL> alter tablespace temp add tempfile '/<path>/temp01.dbf' size 10m ;

Tablespace altered.

SQL> select file#, substr(name, 1, 50), status, enabled from v$tempfile

FILE#    SUBSTR(NAME,1,50)                                  STATUS  ENABLED
-------- -------------------------------------------------- ------- ----------
       1 /<path>/temp01.dbf                                 ONLINE  READ WRITE


(2) It appears that the tablespace was brought offline using "ALTER TABLESPACE USERS OFFLINE" command. So, verify if the missing files really exist with original name. You may need to consult your pear DBAs, or refer alert.log / RMAN backup log or any such information which may provide clue about the actual file name. 

If you find the file, try to rename them. If not, we can offline the datafile or drop associated tablespace:

SQL> select file#, status, enabled, substr(name, 1, 50) from v$datafile where name like '%MISSING%' ;

FILE#    STATUS  ENABLED    SUBSTR(NAME,1,50)
-------- ------- ---------- --------------------------------------------------
       4 OFFLINE DISABLED   /<path>/MISSING000
       7 OFFLINE DISABLED   /<path>/MISSING000


SQL> alter database datafile 4 online ;
alter database datafile 4 online
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01111: name for data file 4 is unknown - rename to correct file
ORA-01110: data file 4: '/<oracle_home path>/dbs/MISSING00004'


SQL> alter database rename file 'MISSING00004' to '/<path>/users01.dbf' ;

Database altered.

SQL> alter database rename file 'MISSING00007' to '/<path>/users02.dbf' ;

Database altered.

SQL> select tablespace_name, status from dba_tablespaces where tablespace_name in (select tablespace_name from dba_data_files where file_id in (4, 7)) ;

TABLESPACE_NAME                STATUS
------------------------------ ---------
USERS                          OFFLINE

SQL> ALTER TABLESPACE USERS ONLINE ;

Tablespace altered.



Before proceeding, let's query the status for these files in alert.log:

SQL> select a.file#, substr(a.name, 1, 50) file_name, a.status file_status, a.error, substr(a.tablespace_name, 1, 10) tablespace_name, b.status tablespace_status from v$datafile_header a, dba_tablespaces b
where a.tablespace_name=b.tablespace_name /* and a.file# in (4, 5, 7, 8, 9) */ ;

FILE# FILE_NAME                                     FILE_STATUS ERROR           TABLESPA TABLESPACE_STATUS
----- --------------------------------------------- ----------- --------------- -------- ------------------
    1 /<path>/system01.dbf                           ONLINE                      SYSTEM   ONLINE
    2 /<path>/sysaux01.dbf                           ONLINE                      SYSAUX   ONLINE
    3 /<path>/undotbs01.dbf                          ONLINE                      UNDOTBS1 ONLINE
    4 /<path>/users01.dbf                            OFFLINE     OFFLINE NORMAL  USERS    OFFLINE <(== related to (2) in alert.log excerpt above
    5 /<path>/example01.dbf                          ONLINE                      EXAMPLE  OFFLINE <(== related to (3) in alert.log excerpt above 
    6 /<path>/mydata01.dbf                           ONLINE                      MYDATA   ONLINE 
    7 /<path>/users02.dbf                            OFFLINE     OFFLINE NORMAL  USERS    OFFLINE <(== related to (2) in alert.log excerpt above 
    8 /<path>1/mydata02.dbf                          OFFLINE     WRONG RESETLOGS MYDATA   ONLINE <(=== related to (4) in alert.log excerpt above 
    9 /<path>/example02.dbf                          ONLINE                      EXAMPLE  OFFLINE <(== related to (3) in alert.log excerpt above

9 rows selected.


So, we can attempt to correct the "ERROR" as displayed in above query depending on the availability of file / archived logs and other possible factors. 

Let's continue,

(3) It seems that tablespace was brought offline inconsistently ( ALTER TABLESPACE EXAMPLE OFFLINE IMMEDIATE ). If the archived log generated at that time has got applied, the file may be back online : 
 

SQL> alter tablespace example ONLINE ;

Tablespace altered.

 

(4) This tablespace MYDATA has 2 datafiles File# 6 & 8. It appears that File# 8 was brought offline ( using ALTER DATABASE DATAFILE 8 OFFLINE ) and it was OFFLINE before OPEN RESETLOGS. If the archived log generated at that time has got applied during recovery or all the archived logs are available for recovery since that time, the file may be back online :

SQL> alter database datafile 8 online ;
alter database datafile 8 online
*
ERROR at line 1:
ORA-01190: control file or data file 8 is from before the last RESETLOGS
ORA-01110: data file 8: '/<path>/mydata02.dbf'


SQL> alter tablespace mydata online ;
alter tablespace mydata online
*
ERROR at line 1:
ORA-01190: control file or data file 8 is from before the last RESETLOGS
ORA-01110: data file 8: '/<path>/mydata02.dbf'


SQL> recover datafile 8 ;
Media recovery complete.
SQL> alter database datafile 8 online ;

Database altered.

SQL> alter tablespace mydata online ;

Tablespace altered.


Please note that it is not always possible to recover and bring the file online which is failing with error " ORA-01190: control file or data file x is from before the last RESETLOGS".


(5) There can be a scenario where the tablespace was in READ ONLY mode before OPEN RESETLOGS. Please check below Article on that:


Note 266991.1 Recovering READONLY tablespace backups made before a RESETLOGS Open

REFERENCES

NOTE:238422.1  - RMAN recover database fails RMAN-6025 - v$archived_log.next_change# is 281474976710655
NOTE:266991.1  - Recovering READONLY tablespace backups made before a RESETLOGS Open

About Me

........................................................................................................................

● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除

● 本文在個人微 信公眾號（ DB寶）上有同步更新

● QQ群號： 230161599 、618766405，微信群私聊

● 個人QQ號（646634621），微 訊號（db_bao），註明新增緣由

● 於 2020年9月 在西安完成

● 最新修改時間：2020年9月

● 版權所有，歡迎分享本文，轉載請保留出處

........................................................................................................................

● 小麥苗的微店： https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

● 小麥苗出版的資料庫類叢書： http://blog.itpub.net/26736162/viewspace-2142121/

● 小麥苗OCP、OCM、高可用、DBA學習班： http://blog.itpub.net/26736162/viewspace-2148098/

● 資料庫筆試面試題庫及解答： http://blog.itpub.net/26736162/viewspace-2134706/

........................................................................................................................

請掃描下面的二維碼來關注小麥苗的微 信公眾號（ DB寶）及QQ群（230161599、618766405）、新增小麥苗微 信（db_bao）， 學習最實用的資料庫技術。

........................................................................................................................

 

 



來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2723840/，如需轉載，請註明出處，否則將追究法律責任。

相關文章