Turnable using hot backup
--- How to gradually recover by archiving to shorten data migration time
Last Updated:
MONDAY, 2004-11-15 10:32
Eygle
Many times you might encounter such a situation: a large database migration, but there is only a few downtime, which looks full of difficulties. However, we can shorten the downtime through various methods.
This example is applicable to the same platform, the same version of the database migration.
In this case, we can use the archive to restore the database to a consistent state by a thermal spare, and the database can be opened by read only. After that, we can continue to apply archive to recover, and finally only short-term downtime, copy the online logs and archive logs in the original database, and control files to the new library, recover, at this time, only a short time is required to complete recovery.
This method can greatly shorten the cutting time. The following is a demonstration step for reference:
Start database
Query archive
SQL> SELECT NAME FROM V $ Archived_log;
Name
-------------------------------------------------- ------------------------------
E: /oracle/oradata/eygle/redo01.log
E: /oracle/oradata/eygle/redo02.log
E: /oracle/oradata/eygle/redo03.log
E: /oracle/oradata/eygle/archive/arc00001.001
E: /oracle/oradata/eygle/archive/arc00002.001
E: /oracle/oradata/eygle/archive/arc00003.001
E: /oracle/oradata/eygle/archive/arc00004.001
E: /oracle/oradata/eygle/archive/arc00005.001
E: /oracle/oradata/eygle/archive/arc00006.001
E: /oracle/oradata/eygle/archive/arc00001.001
E: /oracle/oradata/eygle/archive/arc00002.001
Name
-------------------------------------------------- ------------------------------
E: /oracle/oradata/eygle/archive/arc00003.001
E: /oracle/oradata/eygle/archive/arc00004.001
E: /oracle/oradata/eygle/archive/arc00005.001
E: /oracle/oradata/eygle/archive/arc00006.001
15 lines have been selected.
Archive current log
SQL> ALTER SYSTEM SWITCH LOGFILE;
The system has changed.
2. Backup database
Backup script:
Alter TableSpace System Begin Backup;
Host Copy E: /oracle/oradata/eygle/system01.dbf E: /oracle/orabak/system01.dbf
Alter TableSpace System End Backup;
Alter TableSpace undotbs1 begin backup;
Host Copy E: /oracle/oradata/eygle/undotbs01.dbf E: /oracle/orabak/undotbs01.dbf
Alter TableSpace undotbs1 End Backup;
Alter Tablespace EYGLE BEGIN backup; host copy e: /oracle/oradata/eygle/eygle01.dbf e: /oracle/orabak/eygle01.dbf
Alter TableSpace Eygle End Backup;
Perform a backup:
SQL> @e: /a.sql
The table space has been changed.
1 file has been copied.
The table space has been changed.
The table space has been changed.
1 file has been copied.
The table space has been changed.
The table space has been changed.
1 file has been copied.
The table space has been changed.
The database has been changed.
3. Change the data and archive some logs
SQL> INSERT INTO EYGLE.TEST SELECT * FROM EYGLE.TEST;
224 lines have been created.
SQL> commit;
Submitted.
SQL> ALTER SYSTEM SWITCH LOGFILE;
The system has changed.
SQL> INSERT INTO EYGLE.TEST SELECT * FROM EYGLE.TEST;
448 rows have been created.
SQL> commit;
Submitted.
SQL> ALTER SYSTEM SWITCH LOGFILE;
The system has changed.
SQL> INSERT INTO EYGLE.TEST SELECT * FROM EYGLE.TEST;
896 rows have been created.
SQL> commit;
Submitted.
SQL> Shutdown Immediate;
The database has been closed.
The database has been uninstalled.
Oracle routines have been closed.
4. Perform a recovery of the data file that restores the backup, start the database to use the current log and control file
SQL> Startup Mount;
Oracle routines have been started.
Total System Global Area 47259136 Bytes
Fixed size 454144 bytes
Variable size 29360128 bytes
Database buffers 16777216 BYTES
Redo buffers 667648 bytes
The database is loaded.
SQL> Recover Database Use Backup ControlFile Until Cancel;
ORA-00279: Change 197282 (Generated at 11/13/2004 23:30:48) is required for thread 1
ORA-00289: Recommendation: E: /Oracle/oradata/eygle/archive/arc00008.001
ORA-00280: Change 197282 For thread 1 is made according to sequence # 8
Specify log: {
= SuggeSted | FileName | Auto | Cancel}
ORA-00279: Change 197393 (Generated at 11/13/2004 23:32:22) is required for thread 1
ORA-00289: Suggestions: E: /oracle/oradata/eygle/archive/arc00009.001
ORA-00280: Change 197393 For thread 1 is made according to sequence # 9
ORA-00278: This recovery no longer needs a log file
'E: /oracle/oradata/eygle/archive/arc00008.001'
Specify log: {
= SuggeSted | FileName | Auto | Cancel}
Cancel
Media recovery has been canceled.
SQL>
Alter Database Open Read Only;
The database has been changed.
SQL> Shutdown Immediate;
The database has been closed.
The database has been uninstalled.
Oracle routines have been closed. SQL> Startup Mount;
Oracle routines have been started.
Total System Global Area 47259136 Bytes
Fixed size 454144 bytes
Variable size 29360128 bytes
Database buffers 16777216 BYTES
Redo buffers 667648 bytes
The database is loaded.
SQL> Recover Database Use Backup ControlFile Until Cancel;
ORA-00279: Change 197393 (Generated at 11/13/2004 23:32:22) is required for thread 1
ORA-00289: Suggestions: E: /oracle/oradata/eygle/archive/arc00009.001
ORA-00280: Change 197393 For thread 1 is made according to sequence # 9
Specify log: {
= SuggeSted | FileName | Auto | Cancel}
ORA-00279: Change 197423 (Generated at 11/13/2004 23:32:51) is required for thread 1
ORA-00289: Suggestions: E: /oracle/oradata/eygle/archive/arc00010.001
ORA-00280: Change 197423 For thread 1 is performed in sequence # 10
ORA-00278: This recovery no longer needs a log file
'E: /oracle/oradata/eygle/archive/arc00009.001'
Specify log: {
= SuggeSted | FileName | Auto | Cancel}
Cancel
Media recovery has been canceled.
SQL>
Alter Database Open Read Only;
The database has been changed.
SQL> Shutdown Immediate;
The database has been closed.
The database has been uninstalled.
Oracle routines have been closed.
SQL> Startup Mount;
Oracle routines have been started.
Total System Global Area 47259136 Bytes
Fixed size 454144 bytes
Variable size 29360128 bytes
Database buffers 16777216 BYTES
Redo buffers 667648 bytes
The database is loaded.
SQL> Recover Database Use Backup ControlFile Until Cancel;
ORA-00279: Change 197423 (Generated at 11/13/2004 23:32:51) is required for thread 1
ORA-00289: Suggestions: E: /oracle/oradata/eygle/archive/arc00010.001
ORA-00280: Change 197423 For thread 1 is performed in sequence # 10
Specify log: {
= SuggeSted | FileName | Auto | Cancel}
E: /oracle/oradata/eygle/redo01.log
ORA-00310: Archive log contains sequence 9; request sequence 10
ORA-00334: Archive Log: 'E: /oracle/oradata/eygle/redo01.log'
SQL> Recover Database Use Backup ControlFile Until Cancel;
ORA-00279: Change 197423 (Generated at 11/13/2004 23:32:51) For thread 1 is an essential ORA-00289: Suggest: E: /Oracle/roadata/eygle/archive/arc000.001
ORA-00280: Change 197423 For thread 1 is performed in sequence # 10
Specify log: {
= SuggeSted | FileName | Auto | Cancel} E: /oracle/oradata/eygle/redo02.log The log is applied. Complete media recovery. SQL> ALTABASE OPEN; Alter Database Open * Error is located on line 1: ORA-01589: To turn on the database, you must use the ResetLogs or NoresetLogs option SQL> ALTER DATABASE OPEN RESETLOGS; the database has changed. SQL>
Note that the middle part, Read Only opens not affecting the further recovery of the database. That is to say, the recovery can be performed step by step.
Author: eygle, Oracle technology followers, Oracle technical forum itpub.www.eygle.com from China is the biggest author's personal site you may contact the author by Guoqiang.Gai@gmail.com welcome to explore technical exchanges and links. exchange.
Original source:
http://www.eygle.com/ha/Use.Hot.Backup.recover.day.by.day.htm
