Database Refersh Used HOT backup with in the same Node.

DB Refersh using Contolfile.txt

PROD DB:
--------
SQL> alter database backup controlfile to trace;

Database altered.


SQL> alter database begin backup;

Database altered.

SQL> set line 200
SQL> select * from v$backup;

FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 ACTIVE 1078187 22-SEP-10
2 ACTIVE 1078187 22-SEP-10
3 ACTIVE 1078187 22-SEP-10
4 ACTIVE 1078187 22-SEP-10
5 ACTIVE 1078187 22-SEP-10

SQL> select * from v$backup;

FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 ACTIVE 1078187 22-SEP-10
2 ACTIVE 1078187 22-SEP-10
3 ACTIVE 1078187 22-SEP-10
4 ACTIVE 1078187 22-SEP-10
5 ACTIVE 1078187 22-SEP-10

SQL> alter database end backup;

Database altered.

SQL> alter system switch logfile;

System altered.


GO to TEST DB ->

1. Shutdwon immediate

2. Remove all Datafiels

3. Prepare the control file scripts :

PROD DB control file info/-

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\REDO01.LOG',
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\REDO011.LOG'
) SIZE 50M,
GROUP 2 (
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\REDO02.LOG',
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\REDO02_2A.LOG'
) SIZE 50M,
GROUP 3 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\REDO03.LOG' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSTEM01.DBF',
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\UNDOTBS01.DBF',
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSAUX01.DBF',
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\USERS01.DBF',
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\RAVI1.DBF'
CHARACTER SET WE8MSWIN1252
;



After made changes on the TEST database control file:

STARTUP NOMOUNT

CREATE CONTROLFILE set DATABASE "BUS" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\BUS\REDO01.LOG',
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\BUS\REDO011.LOG'
) SIZE 50M,
GROUP 2 (
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\BUS\REDO02.LOG',
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\BUS\REDO02_2A.LOG'
) SIZE 50M,
GROUP 3 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\BUS\REDO03.LOG' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\BUS\SYSTEM01.DBF',
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\BUS\UNDOTBS01.DBF',
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\BUS\SYSAUX01.DBF',
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\BUS\USERS01.DBF',
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\BUS\RAVI1.DBF'
CHARACTER SET WE8MSWIN1252
;



Connect to Sqlplus /-

SQL> startup nomount
ORACLE instance started.

Total System Global Area 591396864 bytes
Fixed Size 1250308 bytes
Variable Size 171969532 bytes
Database Buffers 411041792 bytes
Redo Buffers 7135232 bytes

SQL> CREATE CONTROLFILE set DATABASE "BUS" RESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 (
9 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\BUS\REDO01.LOG',
10 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\BUS\REDO011.LOG'
11 ) SIZE 50M,
12 GROUP 2 (
13 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\BUS\REDO02.LOG',
14 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\BUS\REDO02_2A.LOG'
15 ) SIZE 50M,
16 GROUP 3 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\BUS\REDO03.LOG' SIZE 50M
17 -- STANDBY LOGFILE
18 DATAFILE
19 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\BUS\SYSTEM01.DBF',
20 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\BUS\UNDOTBS01.DBF',
21 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\BUS\SYSAUX01.DBF',
22 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\BUS\USERS01.DBF',
23 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\BUS\RAVI1.DBF'
24 CHARACTER SET WE8MSWIN1252
25 ;

Control file created.


SQL> recover database using backup controlfile until cancel;
ORA-00279: change 1078187 generated at 09/22/2010 17:05:23 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\BUS\ARCHIVELOG\2010_09_22\O1_MF_1_25_%U_.ARC
ORA-00280: change 1078187 for thread 1 is in sequence #25


Specify log: {=suggested | filename | AUTO | CANCEL}
D:\oracle\product\10.2.0\flash_recovery_area\TEST\ARCHIVELOG\2010_09_22\O1_MF_1_
25_69N3JF1Q_.ARC
ORA-00279: change 1082079 generated at 09/22/2010 19:39:16 needed for thread 1
ORA-00289: suggestion :

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\BUS\ARCHIVELOG\2010_09_22\O1_MF_1_26_%U_.ARC
ORA-00280: change 1082079 for thread 1 is in sequence #26
ORA-00278: log file
'D:\oracle\product\10.2.0\flash_recovery_area\TEST\ARCHIVELOG\2010_09_22\O1_MF_1_25_69N3JF1Q_.ARC'

no longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
cancel =====================================================================>>>>>>>>>>>>>>>>>>>>>>>>>> After there is no arvhive log in PROD DB..
Media recovery cancelled.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

SQL> alter database open resetlogs;

Database altered.

SQL> select name,open_mode,log_mode from v$database;

NAME OPEN_MODE LOG_MODE
--------- ---------- ------------
BUS READ WRITE ARCHIVELOG

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



Regards,
Ravi P