Database Crash during hot standby (9201)
Author: David Zhang (Lunar)
Email: moonlunar@163.com
MSN: lunar52@hotmail.com
Foreword
Hot Spare Use ALTER TABLESPACE XXX Begin Backup; command, this state is merely notified database, and the data files belonging to the tablespace XXX are in the hot standby period, and some data structures of the file header of the data file belonging to the table space are Update, while other parts are frozen. That is, the file content is current, that is, when a table in the file is updated, some database blocks are updated, but when the checkpoint is completed, the SCN value of the checkpoint does not write to the file header, when issued When the ALTER TABLESPACE XXX End Backup; command, the file header is updated, ie, the SCN value of all checkpoints generated during hot standby is updated.
So, the key to this recovery is to find the data file being backed up, and issue
Alter TableSpace XXX End Backup;
To end the hot standby state of the table space.
Check the database archive status
SQL> Archive Log List;
Database log mode archive mode
Automatic archive enable
Archive terminal E: / Oracle / ORADATA / TEST / ARCHIVE
The earliest summary log sequence 81
Next Archive Log Sequence 84
Current log sequence 84
SQL>
Prepare hot spare
Open another window to make test data
C: /> set nls_lang = american_america.us7ascii
C: /> SQLPLUS "/ as sysdba"
SQL * Plus: Release 9.2.0.1.0 - Production on Tue Feb 5 17:17:36 2002
CopyRight (C) 1982, 2002, Oracle Corporation. All Rights Reserved.
Connected to:
Oracle9i
Enterprise
Edition Release 9.2.0.1.0 - Production
With the partitioning, OLAP AND ORACLE DATA MINING OPTIONS
JServer Release 9.2.0.1.0 - Production
SQL> Conn Lunar / Lunar
Connected.
SQL> SELECT *.
No rows selected
SQL> INSERT INTO TEST VALUES (22);
1 row created.
SQL> INSERT INTO TEST VALUES (23);
1 row created.
SQL> commit;
COMMIT COMPLETE.
SQL> SELECT *.
Fly
------------
twenty two
twenty three
SQL> INSERT INTO TEST VALUES (100);
1 row created.
SQL> SELECT *.
Fly
------------
twenty two
twenty three
100
SQL> conn / as sysdba
Connected.
SQL> ALTER System Checkpoint;
SYSTEM altered.
SQL> Archive Log List;
Database log mode archive modeautomatic archival enabled
Archive Destination E: / Oracle / ORADATA / TEST / Archive
Oldest Online Log Sequence 81
Next log sequence to archive 84
Current Log Sequence 84
SQL> Conn Lunar / Lunar
Connected.
SQL> INSERT INTO TEST VALUES (111);
1 row created.
SQL> commit;
COMMIT COMPLETE.
SQL> INSERT INTO TEST VALUES (112);
1 row created.
SQL> SELECT *.
Fly
------------
twenty two
twenty three
100
111
112
SQL>
Open a window, simulate the hot standby database Crash
Microsoft Windows 2000 [Version 5.00.2195]
(C) All rights reserved 1985-2000 Microsoft Corp.
C: /> set nls_lang = american_america.us7ascii
C: /> SQLPLUS "/ as sysdba"
SQL * Plus: Release 9.2.0.1.0 - Production on Tue Feb 5 17:24:55 2002
CopyRight (C) 1982, 2002, Oracle Corporation. All Rights Reserved.
Connected to:
Oracle9i
Enterprise
Edition Release 9.2.0.1.0 - Production
With the partitioning, OLAP AND ORACLE DATA MINING OPTIONS
JServer Release 9.2.0.1.0 - Production
SQL> Shutdown Abort
Oracle Instance Shut Down.
SQL>
Start the database, report an error
SQL> Startup
Oracle Instance Started.
Total System Global Area 135338868 BYtes
Fixed size 453492 bytes
Variable size 109051904 bytes
Database buffers 25165824 BYTES
Redo buffers 667648 bytes
Database mounted.
ORA-01113: File 9 Needs Media Recovery
ORA-01110: Data File 9: E: / ORACLE/ora92/test/rmn01.dbf '
SQL>
Restore database
Check the status of the backup file
SQL> Column filename Format A35
SQL> SELECT A.NAME FileName, B.Status, B.change #, B.Time
2 from V $ DataFile A, V $ Backup B
3 where a.file # = B.file #;
Filename Status Change # TIME
------------------------------------- --- ---------- --------- E: /oracle/ora92/test/system01.dbf Not Active 1045464 05-Feb-02
E: /oracle/ora92/test/undotbs01.dbf Not Active 1045508 05-Feb-02
E: /oracle/ora92/test/drsys01.dbf Not Active 1045521 05-Feb-02
E: /oracle/ora92/test/indx01.dbf Not Active 1045535 05-Feb-02
E: /oracle/ora92/test/tools01.dbf not activive 1045542 05-feb-02
E: /oracle/ora92/test/USERS01.DBF NOT ACTIVE 1045550 05-Feb-02
E: /oracle/ora92/test/xdb01.dbf not active 045558 05-feb-02
E: /oracle/ora92/test/rman01.dbf activive 1045569 05-Feb-02
Rows SELECTED.
SQL>
Restore this file (let it end backup)
SQL> ALTER DATABASE DATAFILE 'E: /Oracle/ora92/test/rmn01.dbf' end backup;
Database altered.
SQL>
Open the database
SQL> ALTABASE OPEN;
Database altered.
SQL>
verify the data
SQL> Archive Log List;
Database log mode archive mode
Automatic archival enabled
Archive Destination E: / Oracle / ORADATA / TEST / Archive
Oldest Online Log Sequence 82
Next log sequence to archive 85
Current Log Sequence 85
SQL> Conn Lunar / Lunar
Connected.
SQL> SELECT *.
Fly
------------
twenty two
twenty three
100
111
SQL>
