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
Reply With Quote