Recovering data from last monday's backup

[pst]

Is it possible to apply the archive logs to the developemnet database to bring it to last monday's live database status.

Our client using the live database wants to recover some data from last monday. But they want to use the live database with current data as well. So, I was just wondering if it was possible to recover the required data to the developement database.
Can you tell me the best way to go about this.

Thanks a lot

pst

[pando]

nop, may be you could try logminer to recover some data manually but definitely not recovering archived logs because all scn are different, database name etc etc

[pst]

thanks Pando. Do you think I can copy the datafiles from backup to my developemnt database???

[pando]

no because they are not the same, Oracle checks file headers when you start the database, now dont ask me what file headers contaisn because I only know a bit but scn is definitely one which is not same in both database

[pst]

Can you also please tell me how to use logminer for this purpose? The anlaysts want to just check if some particular data existed last monday.

thanks a lot
pst

[pando]

-- Set UTL_FILE_DIR in initSID.ora
Bounce the database

-- Build the dictionary
EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora', '/oracle/admin/inmo/utl_file') 

-- Add the logs
EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/datos1/INMO/redo/redo1_1.log', DBMS_LOGMNR.NEW)
EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/datos1/INMO/redo/redo2_1.log', DBMS_LOGMNR.ADDFILE)
EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/datos1/desa/redo2_1.log', DBMS_LOGMNR.ADDFILE)

-- Mine the logs
EXECUTE DBMS_LOGMNR.START_LOGMNR( - 
DICTFILENAME => '/oracle/admin/inmo/utl_file/dictionary.ora', - 
STARTTIME => to_date('25-03-2001 01:38:00','DD-MM-YYYY HH:MI:SS'), - 
ENDTIME => to_date('26-03-2001 09:00:00','DD-MM-YYYY HH:MI:SS'));

Now, this is used to *READ* logs, the logs contains instructions to recover transactions but the data are not there so I dont think this would work for your purpose and I dont think there is any other way to look datafile contents

[molonede]

You could use the backups to create a new database and then recover to a point in time (Monday). You then could use a dblink to insert the data back into the original system.

[pst]

I can try that. But how do I go about that ? If I create a new database, do I use these backup datafiles? Wont the file headers be pointing to the old database.
Can you please help me with this and how do I recover to a point in time also?

Thanks a lot for the help,

pst

[reydp]

You can recreate a 2nd database by first generating a trace file to
recreate control file by issuing command to your current database:
svrmgrl>alter database backup controlfile to trace;

Then find that trace file in bdump directory and rename(RECREATE_CTL.sql):
edit the following example:

DATABASE "PRD" - "TST"
RECOVER DATABASE - RECOVER DATABASE UNTIL TIME 'YYYY-MM-DD:HH24:MI:SS'

And the new folder for the TST Database for all datafiles example:

/u02/app/oradata/PRD/system01.dbf - /u02/app/oradata/TST/system01.dbf


Copy your INITORA file and edit your 2nd INITORA file(INITTST.ora)
db_name
control_file
log_archive_dest

Note : Always set your ORACLE_SID environment to TST before executing this script
You might end up loosing your current DB.
unix:
$ORACLE_SID=TST; export ORACLE_SID
windows:
DOS>set ORACLE_SID=TST /* I can't remember if it is right*/

invoke SERVER MANAGER
$svrmgrl
DOS>svrmgrl
connect as sysdba or internal
svrmgrl>connect internal/oracle


Startup the database in just MOUNT mode
svrmgrl>startup mount pfile=INITTST.ora /* this may include directory*/

and execute that script RECREATE_CTL.sql

svrmgrl>start RECREATE_CTL.sql




Note: This assumes that you have a lot of disk space to spare


And follow what molonede says about dblink.

Good Luck.