re:datafiles recovery
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: re:datafiles recovery

  1. #1
    Join Date
    May 2008
    Posts
    2

    re:datafiles recovery

    Gurus,

    Case:
    I am actually to see how i can implement one of my readings on a single stand alone system (i.e my laptop)

    The text reading states how to recover datafiles or tablespace that has not been back up and it is thus stated below...........


    Recovery Without a Backup Example

    Datafile 4 (on disk 1) is lost. When restoring the datafile from tape, you receive an error
    indicating that the file was not backed up. You locate the DBA who created the
    TABLE_DATA tablespace two days ago and find that it contains important user data, but it
    was never included in the backup strategy. Because datafile 4 is not a system or rollback
    segment data file, and you have all archived logs for the past two days, you can proceed as
    follows:


    1. If the database is closed, then mount the database, take the datafile (with no backup)
    offline, and open the database. This allows users who do not need the TABLE_DATA
    tablespace to work on the system. If the database is open, take the datafile or the
    tablespace offline.
    Note: If the database is open, the immediate option must be included, to avoid the
    database writer trying to write to a file that does not exist:
    SQL> ALTER TABLESPACE table_data OFFLINE IMMEDIATE;
    Tablespace altered.

    You confirm the recovery status by querying V$RECOVER_FILE to check the status of
    a backup:
    SQL> SELECT * FROM v$recover_file;
    FILE# ONLINE ERROR CHANGE# TIME
    ----- ------- -------------- ------- ----
    4 OFFLINE FILE NOT FOUND 0



    2. You issue the following command to re-create the file:
    SQL> ALTER DATABASE create datafile ‘/disk2/DATA/df4.dbf’
    2> as ‘/disk1/DATA/df4.dbf‘;
    Database altered.
    SQL> SELECT * FROM v$recover_file;
    FILE# ONLINE ERROR CHANGE# TIME
    ----- ------- ------------------ ---------- ------
    4 OFFLINE 248621 01-DEC-97




    3. Use the RECOVER or ALTER DATABASE RECOVER commands to start applying the
    archived and online redo log files to the re-created datafile:
    SQL> RECOVER TABLESPACE table_data;
    To bring the datafile to the point of failure, all needed archived logs and redo logs are
    applied. All datafiles are now synchronized.



    4. When recovery is finished, bring the tablespace online:
    SQL> ALTER TABLESPACE table_data ONLINE;
    All data is now recovered. Include the file in the backup strategy and notify users that the
    tablespace is ready to be used again.


    I later tried to implement the above on my system by taken of or cutting of my users01.dbf from my database /oracle_home/ and change the location .

    Although , i did not implement any additonal DML language ( either by inserting or updating any table in the database. hence my log sequence did not change , and i have the following reports on my sql plus prompt----------


    SQL*Plus: Release 10.2.0.3.0 - Production on Fri May 23 03:42:40 2008

    Copyright (c) 1982, 2006, Oracle. All Rights Reserved.


    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
    With the Partitioning, OLAP and Data Mining options

    SQL> show sga

    Total System Global Area 251658240 bytes
    Fixed Size 1289964 bytes
    Variable Size 205521172 bytes
    Database Buffers 37748736 bytes
    Redo Buffers 7098368 bytes
    SQL> show user
    USER is "HR"
    SQL> connect / as sysdba
    Connected.
    SQL> shutdown immediate
    Database closed.
    Database dismounted.
    ORACLE instance shut down.

    SQL> startup mount;
    ORA-32004: obsolete and/or deprecated parameter(s) specified
    ORACLE instance started.

    Total System Global Area 251658240 bytes
    Fixed Size 1289964 bytes
    Variable Size 209715476 bytes
    Database Buffers 33554432 bytes
    Redo Buffers 7098368 bytes
    Database mounted.

    SQL> archive log list;
    Database log mode Archive Mode
    Automatic archival Enabled
    Archive destination USE_DB_RECOVERY_FILE_DEST
    Oldest online log sequence 20
    Next log sequence to archive 22
    Current log sequence 22
    SQL> alter database datafile 'c:\oracle\product\10.2.0\oradata\timi2\users01.dbf' offline;

    Database altered.

    SQL> alter database open;

    Database altered.


    SQL> alter tablespace users offline immediate;

    Tablespace altered.



    SQL> select * from v$recover_file;

    FILE# ONLINE ONLINE_
    ---------- ------- -------
    ERROR CHANGE#
    ----------------------------------------------------------------- ----------
    TIME
    ---------
    4 OFFLINE OFFLINE
    FILE NOT FOUND 0



    SQL> alter database create datafile'c:\oracle\product\10.2.0\oradata\users01.dbf' as 'c:\oracle\prod
    uct\10.2.0\oradata\timi2\users01.dbf';
    alter database create datafile'c:\oracle\product\10.2.0\oradata\users01.dbf' as 'c:\oracle\product\1
    *
    ERROR at line 1:
    ORA-01516: nonexistent log file, datafile, or tempfile
    "c:\oracle\product\10.2.0\oradata\users01.dbf"



    SQL> archive log list;
    Database log mode Archive Mode
    Automatic archival Enabled
    Archive destination USE_DB_RECOVERY_FILE_DEST
    Oldest online log sequence 20
    Next log sequence to archive 22
    Current log sequence 22
    SQL>


    My concern is how can i successfully implement a datafile or tablespace loss recovery without having a second hard disk?

    Or how do i implement my recovery on my stand alone system with just one hard disk. I have been able to implement Backup successflly using both RMAN and USER managed backup?
    Thanks

  2. #2
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    First of all that is not the way you should do in 10g!

    Here you go. My database is
    Code:
    SQL> select * from v$version ;
    
    BANNER
    ----------------------------------------------------------------
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
    PL/SQL Release 10.2.0.1.0 - Production
    CORE    10.2.0.1.0      Production
    TNS for Solaris: Version 10.2.0.1.0 - Production
    NLSRTL Version 10.2.0.1.0 - Production
    
    SQL> archive log list
    Database log mode              Archive Mode
    Automatic archival             Enabled
    Archive destination            /oradata/oradata/rbk/archive
    Oldest online log sequence     220
    Next log sequence to archive   222
    Current log sequence           222
    
    SQL> select file_name from dba_data_files ;
    
    FILE_NAME
    --------------------------------------------------------------------------------
    /oradata/oradata/rbk/rbk/system01.dbf
    /oradata/oradata/rbk/rbk/undotbs01.dbf
    /oradata/oradata/rbk/rbk/sysaux01.dbf
    /oradata/oradata/rbk/rbk/users01.dbf
    /oradata/oradata/rbk/rbk/lucky.dbf
    I took a backup then,
    Code:
    oracle@:/oradata/backup/rman>$ rman  target / nocatalog
    
    Recovery Manager: Release 10.2.0.1.0 - Production on Tue May 27 20:58:36 2008
    
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
    
    connected to target database: RBK (DBID=2645839377)
    using target database control file instead of recovery catalog
    
    RMAN> backup database plus archivelog format '/oradata/backup/rman/%U';
    
    
    Starting backup at 27-MAY-08
    current log archived
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting archive log backupset
    channel ORA_DISK_1: specifying archive log(s) in backup set
    input archive log thread=1 sequence=39 recid=1 stamp=649020740
    input archive log thread=1 sequence=221 recid=285 stamp=655829633
    input archive log thread=1 sequence=222 recid=286 stamp=655851564
    input archive log thread=1 sequence=223 recid=287 stamp=655851647
    channel ORA_DISK_1: starting piece 1 at 27-MAY-08
    channel ORA_DISK_1: finished piece 1 at 27-MAY-08
    piece handle=/oradata/backup/rman/02jhf040_1_1 tag=TAG20080527T210047 comment=NONE
    channel ORA_DISK_1: backup set complete, elapsed time: 00:00:16
    Finished backup at 27-MAY-08
    
    Starting backup at 27-MAY-08
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting full datafile backupset
    channel ORA_DISK_1: specifying datafile(s) in backupset
    input datafile fno=00001 name=/oradata/oradata/rbk/rbk/system01.dbf
    input datafile fno=00003 name=/oradata/oradata/rbk/rbk/sysaux01.dbf
    input datafile fno=00002 name=/oradata/oradata/rbk/rbk/undotbs01.dbf
    input datafile fno=00005 name=/oradata/oradata/rbk/rbk/lucky.dbf
    input datafile fno=00004 name=/oradata/oradata/rbk/rbk/users01.dbf
    channel ORA_DISK_1: starting piece 1 at 27-MAY-08
    channel ORA_DISK_1: finished piece 1 at 27-MAY-08
    piece handle=/oradata/oradata/rbk/flash_recovery_area/RBK/backupset/2008_05_27/o1_mf_nnndf_TAG20080527T210104_43rb9ysn_.bkp tag=TAG20080527T210104 comment=NONE
    channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
    channel ORA_DISK_1: starting full datafile backupset
    channel ORA_DISK_1: specifying datafile(s) in backupset
    including current control file in backupset
    including current SPFILE in backupset
    channel ORA_DISK_1: starting piece 1 at 27-MAY-08
    channel ORA_DISK_1: finished piece 1 at 27-MAY-08
    piece handle=/oradata/oradata/rbk/flash_recovery_area/RBK/backupset/2008_05_27/o1_mf_ncsnf_TAG20080527T210104_43rbd5cn_.bkp tag=TAG20080527T210104 comment=NONE
    channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
    Finished backup at 27-MAY-08
    
    Starting backup at 27-MAY-08
    current log archived
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting archive log backupset
    channel ORA_DISK_1: specifying archive log(s) in backup set
    input archive log thread=1 sequence=224 recid=288 stamp=655851745
    channel ORA_DISK_1: starting piece 1 at 27-MAY-08
    channel ORA_DISK_1: finished piece 1 at 27-MAY-08
    piece handle=/oradata/backup/rman/05jhf071_1_1 tag=TAG20080527T210225 comment=NONE
    channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
    Finished backup at 27-MAY-08
    Add a new file name and switch and no backup for the new file! I deleted the file so that I put myself in trouble!

    Code:
    SQL> select file_name ,tablespace_name from dba_data_files
    
    FILE_NAME                                          TABLESPACE_NAME
    -------------------------------------------------- ------------------------------
    /oradata/oradata/rbk/rbk/system01.dbf              SYSTEM
    /oradata/oradata/rbk/rbk/undotbs01.dbf             UNDOTBS1
    /oradata/oradata/rbk/rbk/sysaux01.dbf              SYSAUX
    /oradata/oradata/rbk/rbk/users01.dbf               USERS
    /oradata/oradata/rbk/rbk/lucky.dbf                 LUCKY
    
    
    
    SQL> alter tablespace LUCKY add datafile '/oradata/oradata/rbk/rbk/lucky_new.dbf' size 50m ;
    
    Tablespace altered.
    
    
    
    SQL> alter system switch logfile;
    
    System altered.
    
    SQL>  alter system switch logfile;
    
    System altered.
    
    SQL> alter system switch logfile;
    
    System altered.
    
    
    rm /oradata/oradata/rbk/rbk/lucky_new.dbf 
    
    oracle@:/oradata/backup/rman>$ sqlplus
    
    SQL*Plus: Release 10.2.0.1.0 - Production on Tue May 27 21:06:09 2008
    
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
    
    Enter user-name: / as sysdba
    
    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
    With the Partitioning, OLAP and Data Mining options
    
    SQL> shutdown immediate ;
    ORA-01116: error in opening database file 6
    ORA-01110: data file 6: '/oradata/oradata/rbk/rbk/lucky_new.dbf'
    ORA-27041: unable to open file
    SVR4 Error: 2: No such file or directory
    Additional information: 3
    
    
    SQL> alter database datafile 6 offline;
    
    Database altered.
    
    
    SQL>  select * from v$recover_file;
    
         FILE# ONLINE  ONLINE_
    ---------- ------- -------
    ERROR                                                                CHANGE#
    ----------------------------------------------------------------- ----------
    TIME
    ---------
             6 OFFLINE OFFLINE
    FILE NOT FOUND
    RMAN did the trick for me. I just said restore and sisnce there is no file in the backup, RMAN created the datafile sisnce I got all the archive log files. It is not like the previous versions - 10g got changed
    oracle@:/oradata/backup/rman>$ rman target / nocatalog

    Recovery Manager: Release 10.2.0.1.0 - Production on Tue May 27 21:08:25 2008

    Copyright (c) 1982, 2005, Oracle. All rights reserved.

    connected to target database: RBK (DBID=2645839377)
    using target database control file instead of recovery catalog


    RMAN> restore datafile 6;

    Starting restore at 27-MAY-08
    using channel ORA_DISK_1

    creating datafile fno=6 name=/oradata/oradata/rbk/rbk/lucky_new.dbf
    restore not done; all files readonly, offline, or already restored
    Finished restore at 27-MAY-08

    RMAN> recover datafile 6;

    Starting recover at 27-MAY-08
    using channel ORA_DISK_1

    starting media recovery

    archive log thread 1 sequence 225 is already on disk as file /oradata/oradata/rbk/archive/1_225_648753361.dbf
    archive log thread 1 sequence 226 is already on disk as file /oradata/oradata/rbk/archive/1_226_648753361.dbf
    archive log thread 1 sequence 227 is already on disk as file /oradata/oradata/rbk/archive/1_227_648753361.dbf
    archive log filename=/oradata/oradata/rbk/archive/1_225_648753361.dbf thread=1 sequence=225
    media recovery complete, elapsed time: 00:00:03
    Finished recover at 27-MAY-08

    RMAN> sql 'alter database datafile 6 online ';

    sql statement: alter database datafile 6 online


    SQL> shutdown immediate ;

    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> SQL> startup
    ORACLE instance started.

    Total System Global Area 293601280 bytes
    Fixed Size 1978144 bytes
    Variable Size 121639136 bytes
    Database Buffers 163577856 bytes
    Redo Buffers 6406144 bytes
    Database mounted.
    Database opened.

    SQL> select file_name,status from dba_data_files


    FILE_NAME STATUS
    -------------------------------------------------- ---------
    /oradata/oradata/rbk/rbk/system01.dbf AVAILABLE
    /oradata/oradata/rbk/rbk/undotbs01.dbf AVAILABLE
    /oradata/oradata/rbk/rbk/sysaux01.dbf AVAILABLE
    /oradata/oradata/rbk/rbk/users01.dbf AVAILABLE
    /oradata/oradata/rbk/rbk/lucky.dbf AVAILABLE
    /oradata/oradata/rbk/rbk/lucky_new.dbf AVAILABLE
    Recovery completed in minimum effort even without a backup... Thanks to Oracle!
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width