When Several Datafiles Are Missing – Restore From the Standby Database

  • by
Not only the local backups can save the datafiles, but also the standby database can be a source to restore the datafiles. There are four major steps that can accomplish this:
  • Copy datafiles from the standby database.
  • Catalog them.
  • Switch them.
  • Recover them.
Here is a demonstration below:
  1. Stop the primary database.
  2. [oracle@primary01 ~]$ srvctl stop database -d compdb
  3. Remove several datafiles to simulate this scenario.
  4. [oracle@primary02 ~]$ asmcmd
    ASMCMD> cd +data/primdb/datafile
    ASMCMD> ls
    EXAMPLE.900.797943711
    SYSAUX.898.797943569
    SYSTEM.897.797943475
    UNDOTBS1.899.797943687
    UNDOTBS2.901.797943737
    USERS.902.797943753
    ASMCMD> rm USERS.902.797943753
    ASMCMD> rm EXAMPLE.900.797943711
    ASMCMD> ls
    SYSAUX.898.797943569
    SYSTEM.897.797943475
    UNDOTBS1.899.797943687
    UNDOTBS2.901.797943737
    ASMCMD>

  5. Try to startup an instance of the primary cluster database. It will fail as expected.
  6. [oracle@primary01 ~]$ sqlplus / as sysdba
    ...
    Connected to an idle instance.

    SQL> startup
    ORACLE instance started.

    Total System Global Area  839282688 bytes
    Fixed Size                  2217992 bytes
    Variable Size             792725496 bytes
    Database Buffers           41943040 bytes
    Redo Buffers                2396160 bytes
    Database mounted.
    ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
    ORA-01110: data file 4: '+DATA/primdb/datafile/users.902.797943753'
    The database can only be mounted now.

  7. Connect to target and auxiliary database with RMAN.
  8. Please note that, now the target database is the standby database, the auxiliary database is the primary database.
    [oracle@standby01 ~]$ rman target / auxiliary sys/password@primdb1
    ...
    connected to target database: COMPDB (DBID=841830157, not open)
    connected to auxiliary database: COMPDB (DBID=841830157, not open)

    Both databases are mounted, not open.
  9. Copy the datafiles from the target (standby) to auxiliary (primary) database.
  10. RMAN> backup as copy datafile 4,5 auxiliary format '+data';

    Starting backup at 22-JAN-13
    using target database control file instead of recovery catalog
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=82 instance=standb1 device type=DISK
    channel ORA_DISK_1: starting datafile copy
    input datafile file number=00005 name=+DATA/standb/datafile/example.270.799012469
    output file name=+DATA/primdb/datafile/example.900.805375051 tag=TAG20130122T111730
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:26
    channel ORA_DISK_1: starting datafile copy
    input datafile file number=00004 name=+DATA/standb/datafile/users.268.799012479
    output file name=+DATA/primdb/datafile/users.902.805375077 tag=TAG20130122T111730
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
    Finished backup at 22-JAN-13

  11. Try to open the primary database again, but failed.
  12. SQL> alter database open;
    alter database open
    *
    ERROR at line 1:
    ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
    ORA-01110: data file 4: '+DATA/primdb/datafile/users.902.797943753'

    It’s reasonable that the database knows nothing about the newly added datafile copies before cataloging and switching them.
  13. Connect to the primary database with RMAN.
  14. [oracle@primary01 ~]$ rman target /
    ...
    connected to target database: COMPDB (DBID=841830157, not open)

  15. Catalog the new arrivals.
  16. You can catalog the two datafiles explicitly.
    RMAN> catalog datafilecopy '+data/primdb/datafile/USERS.902.805375077';

    cataloged datafile copy
    datafile copy file name=+DATA/primdb/datafile/users.902.805375077 RECID=16 STAMP=805375765

    RMAN> catalog datafilecopy '+data/primdb/datafile/EXAMPLE.900.805375051';

    cataloged datafile copy
    datafile copy file name=+DATA/primdb/datafile/example.900.805375051 RECID=17 STAMP=805375794

    Another way to catalog the two datafiles is to use START WITH clause.
    RMAN> catalog start with '+data/primdb/datafile';

    searching for all files that match the pattern +data/primdb/datafile

    List of Files Unknown to the Database
    =====================================
    File Name: +data/primdb/DATAFILE/EXAMPLE.1120.805383723
    File Name: +data/primdb/DATAFILE/USERS.1121.805383737

    Do you really want to catalog the above files (enter YES or NO)? yes
    cataloging files...
    cataloging done

    List of Cataloged Files
    =======================
    File Name: +data/primdb/DATAFILE/EXAMPLE.1120.805383723
    File Name: +data/primdb/DATAFILE/USERS.1121.805383737

  17. Switch to new copies.
  18. You can catalog the two datafiles one by one explicitly.
    RMAN> switch datafile 4 to copy;

    datafile 4 switched to datafile copy "+DATA/primdb/datafile/users.902.805375077"

    RMAN> switch datafile 5 to copy;

    datafile 5 switched to datafile copy "+DATA/primdb/datafile/example.900.805375051"

    Or you can do it together.
    RMAN> switch datafile 4,5 to copy;

    datafile 4 switched to datafile copy "+DATA/primdb/datafile/users.1121.805383737"
    datafile 5 switched to datafile copy "+DATA/primdb/datafile/example.1120.805383723"

    If there are too many unmemorable datafiles needed to be switched, I suggest you to copy all the database from the standby and switch database instead.
  19. Recover the datafiles.
  20. Although the working datafiles point to the right copies, they can not be used in this moment, they needs to be recovered.
    RMAN> recover database;

    Starting recover at 22-JAN-13
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=38 instance=primdb1 device type=DISK

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

    Finished recover at 22-JAN-13

  21. Alter the primary database open.
  22. RMAN> alter database open;

    database opened

    Yes, all done.
More related posts that you may be interested in:

Leave a Reply

Your email address will not be published. Required fields are marked *