| Want to do Point in time Recovery--on Productin DB [message #136865] |
Sun, 11 September 2005 23:17  |
bhavind
Messages: 8
Registered: September 2005
|
Junior Member |
|
|
Hi frends,
I want to do point in time recovery.
Task : I want to refresh my db up to time 6th sep 05. I have a cold backup up to 5th sep 05. and all archive files after 5th sep 05. so please tell me step by step method to recover my database up to 6th sep 05.
I am writing my steps ..pl. correct me if i am wrong..
1) restore 5 th september backup.
2) conn sys
startup mount
recover database until time '06/11/05 24:00:00'
3) alter database open;
Pl. tell me i have to use recovery using backup controlfile ??
Thanks
|
|
|
|
| Re: Want to do Point in time Recovery--on Productin DB [message #136868 is a reply to message #136865] |
Mon, 12 September 2005 00:04  |
tarundua
Messages: 1080
Registered: June 2005
Location: India
|
Senior Member |
|
|
As i know the steps are fine but ur command :
>> recover database until time '06/11/05 24:00:00'
here the time format should be 'YYYY-MM-DD:HH24:MI:SS' ( it is mentioned in the Backup & recovery book )
and if u have the current control file then u dnt need backup control file.
>> alter database open;
And because u r doing incomplete recovery , the database should be open in resetlogs mode.
Alter database open resetlogs;
regards,
tarun
|
|
|
|
|
|
| Re: Want to do Point in time Recovery--on Productin DB [message #136870 is a reply to message #136869] |
Mon, 12 September 2005 00:32 |
tarundua
Messages: 1080
Registered: June 2005
Location: India
|
Senior Member |
|
|
this is an excerpt from Oracle Documentation :
| Quote: |
1. Issue the RECOVER DATABASE UNTIL TIME statement to begin time-based
recovery. The time is always specified using the following format, delimited by
single quotation marks: 'YYYY-MM-DD:HH24:MI:SS'. The following
statement recovers the database up to a specified time:
RECOVER DATABASE UNTIL TIME '2000-12-31:12:47:30'
If a backup of the control file is being used with this incomplete recovery (that
is, a control file backup or re-created control file was restored), then indicate this
in the statement used to start recovery. The following statement recovers the
database up to a specified time using a control file backup:
RECOVER DATABASE UNTIL TIME '2000-12-31:12:47:30' USING BACKUP CONTROLFILE
|
If the current control files do not match the physical structure of the database at
the intended time of recovery, then restore a backup control file.
So if there is no structural change in the database after 5th september then there is no need to restore any backup control file.
But before performing on production DB, wait for other people comments also.
regards,
tarun
|
|
|
|
|
|
| Re: Want to do Point in time Recovery--on Productin DB [message #136883 is a reply to message #136873] |
Mon, 12 September 2005 01:47 |
tarundua
Messages: 1080
Registered: June 2005
Location: India
|
Senior Member |
|
|
Changes , i mean to say physical/structural changes in the database such as addition/deletion of a tablespace, datafile ,logfile etc.
It doesnt mean that u created a new table or inserted records, did some DML .
So if there is no physical change then i think u can use the control file of today itself but still if u r going to implement on a production database then take the backup of whole database as of now before implementing any procedures and make urself safe.
regards,
tarun
|
|
|
|
| Re: Want to do Point in time Recovery--on Productin DB [message #136889 is a reply to message #136865] |
Mon, 12 September 2005 02:05 |
bhavind
Messages: 8
Registered: September 2005
|
Junior Member |
|
|
Hi tarun,
is there any concept of SCN.. because suppose in cold backup SCN count 1000 and i intend to recover database until 6 sep.. so it will implement 1 day transaction so it's SCN won't be 1000 it will be more than 1000. so in this scenario do i need to restore a control file of 6th sep.
hope you understand my prob..
|
|
|
|
|
|
| Re: Want to do Point in time Recovery--on Productin DB [message #137705 is a reply to message #136865] |
Fri, 16 September 2005 03:15 |
alexzeng
Messages: 133
Registered: August 2005
Location: alexzeng.wordpress.com
|
Senior Member |
|
|
I don't know whether you use rman.
I have a test of your case using RMAN on my test machine.
The detail processes:
--step1. set the test environment. check the backup and update table dual
--list backup
[oracle@rhes31 oracle]$ rman nocatalog target /
--it's backuped at 09-SEP-05
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4 Full 534M DISK 00:01:51 09-SEP-05
BP Key: 4 Status: AVAILABLE Tag: TAG20050909T114652
Piece Name: /opt/oracle/rman/std31_05gu6stc_1_1
List of Datafiles in backup set 4
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 6995866 09-SEP-05 /opt/oracle/oradata/std1/system01.dbf
2 Full 6995866 09-SEP-05 /opt/oracle/oradata/std1/undotbs01.dbf
3 Full 6995866 09-SEP-05 /opt/oracle/oradata/std1/cwmlite01.dbf
4 Full 6995866 09-SEP-05 /opt/oracle/oradata/std1/drsys01.dbf
5 Full 6995866 09-SEP-05 /opt/oracle/oradata/std1/example01.dbf
6 Full 6995866 09-SEP-05 /opt/oracle/oradata/std1/indx01.dbf
7 Full 6995866 09-SEP-05 /opt/oracle/oradata/std1/odm01.dbf
8 Full 6995866 09-SEP-05 /opt/oracle/oradata/std1/tools01.dbf
9 Full 6995866 09-SEP-05 /opt/oracle/oradata/std1/users01.dbf
10 Full 6995866 09-SEP-05 /opt/oracle/oradata/std1/xdb01.dbf
11 Full 6995866 09-SEP-05 /opt/oracle/oradata/rcat01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5 Full 1M DISK 00:00:01 09-SEP-05
BP Key: 5 Status: AVAILABLE Tag:
Piece Name: /opt/oracle/rman/std31_cf_c-3008016294-20050909-00
SPFILE Included: Modification time: 08-SEP-05
--update table dual
[oracle@rhes31 oracle]$ sqlplus /nolog
SQL> select * from dual;
D
-
X
SQL> update dual set dummy='Y';
1 row updated.
SQL> commit;
Commit complete.
SQL> select sysdate from dual;
SYSDATE
---------
16-SEP-05
--step2.restore to 12-SEP-05
--if you don't use to_date funciton in "set until time" clause,
-- you'd better set the two parameter bellow according to your db
--NLS_LANG=american_america.US7ASCII
--NLS_DATE_FORMAT='Mon DD YYYY HH24:MI:SS'
[oracle@rhes31 oracle]$ rman nocatalog target /
Recovery Manager: Release 9.2.0.6.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database (not started)
RMAN> startup mount
Oracle instance started
database mounted
Total System Global Area 236000476 bytes
Fixed Size 451804 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
RMAN> run
2> {
3> set until time "to_date('2005-09-12 00:00:00','yyyy-mm-dd hh24:mi:ss')";
4> restore database;
5> recover database;
6> alter database open resetlogs;
7> }
executing command: SET until clause
Starting restore at 16-SEP-05
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=13 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /opt/oracle/oradata/std1/system01.dbf
restoring datafile 00002 to /opt/oracle/oradata/std1/undotbs01.dbf
restoring datafile 00003 to /opt/oracle/oradata/std1/cwmlite01.dbf
restoring datafile 00004 to /opt/oracle/oradata/std1/drsys01.dbf
restoring datafile 00005 to /opt/oracle/oradata/std1/example01.dbf
restoring datafile 00006 to /opt/oracle/oradata/std1/indx01.dbf
restoring datafile 00007 to /opt/oracle/oradata/std1/odm01.dbf
restoring datafile 00008 to /opt/oracle/oradata/std1/tools01.dbf
restoring datafile 00009 to /opt/oracle/oradata/std1/users01.dbf
restoring datafile 00010 to /opt/oracle/oradata/std1/xdb01.dbf
restoring datafile 00011 to /opt/oracle/oradata/rcat01.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/opt/oracle/rman/std31_05gu6stc_1_1 tag=TAG20050909T114652 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 16-SEP-05
Starting recover at 16-SEP-05
using channel ORA_DISK_1
starting media recovery
media recovery complete
Finished recover at 16-SEP-05
database opened
--step3.check the database, done.
[oracle@rhes31 oracle]$ sqlplus '/ as sysdba'
SQL*Plus: Release 9.2.0.6.0 - Production on Fri Sep 16 16:12:52 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
s
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
SQL> elect * from dual;
D
-
X
--it's the old data "X"
[Updated on: Fri, 16 September 2005 03:24] Report message to a moderator |
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
