2 If the LGWR is at least access to a member, the write to the member accessible within the group will be performed as usual, and the LGWR ignores unavailable members within the group. If the group is inactive, that is, the checkpoint has been completed, then discard and add a new online log member to solve the problem, otherwise, if the group is the current active log group, you must first force the log switching.
SQL> SELECT Group #, Sequence #, Bytes, Members, STATUS
2 from V $ log;
Group # sequence # bytes Members Status
---------- -------------------------------------- --------
1 411 1048576 2 inactive
2 412 1048576 2 inactive
3 413 1048576 2 inactive
5 414 1048576 2 CURRENT
SQL> SELECT * FROM V $ logfile
2 ORDER by group #;
Group # status member
---------- --------------------------------------- -------------------------
1 D: /oracle1/ora81/oradata/test/redo01.log
1 E: /oracle1/ora81/oradata/test/redo04.log
2 D: /oracle1/ora81/oradata/test/redo02.log
2 E: /oracle1/ora81/oradata/test/redo05.log
3 D: /oracle1/ora81/oradata/test/redo03.log
3 E: /oracle1/ora81/oradata/test/redo06.log
5 E: /oracle1/ora81/oradata/test/redo07.log
5 E: /oracle1/ora81/oradata/test/redo08.log
8 rows success
SQL>
In order to simulate a member damage in the log group, we open the text editor and destroy the E: /oracle1/ora81/oradata/test/redo07.log file, then we see:
SQL> SELECT * FROM V $ logfile;
Group # status member
---------- --------------------------------------- ----------------------
1 D: /oracle1/ora81/oradata/test/redo01.log
2 D: /oracle1/ora81/oradata/test/redo02.log
3 D: /oracle1/ora81/oradata/test/redo03.log
1 E: /oracle1/ora81/oradata/test/redo04.log
2 E: /oracle1/ora81/oradata/test/redo05.log
3 E: /oracle1/ora81/oradata/test/redo06.log
5 INVALID E: /oracle1/ora81/oradata/test/redo07.log5 E: /oracle1/ora81/oradata/test/redo08.log
8 rows success
SQL>
But this time the data can also ignore this damaged file and normal use. To fix this file, we need to do:
SQL> Shutdown
The database has been closed.
The database has been uninstalled.
Oracle routines have been closed.
SQL> Host
Microsoft Windows 2000 [Version 5.00.2195]
(C) All rights reserved 1985-2000 Microsoft Corp.
E: /> COPY E: / ORACLE1/ora81/oradata/test/redo08.log E: / Oracle1 / Ora81 / ORADATA / TEST /
Redo07.log
Change E: / ORACLE1/ora81/oradata/test/redo07.log? (Yes / no / all): YES
1 file has been copied.
The above steps need to be noted that if the location or file name of the new log file needs to change (eg, the media failure), rename the log file of this change position or name after the Startup Mount, then Open the data again, see "Repositioning or renameting the online redo log file".
E: /> EXIT
SQL> Startup
Oracle routines have been started.
Total System Global Area 36214812 BYtes
Fixed Size 75804 BYTES
Variable size 19283968 bytes
Database buffers 16777216 BYTES
Redo buffers 77824 bytes
The database is loaded.
The database has been opened.
SQL> SELECT * FROM V $ logfile;
Group # status member
---------- --------------------------------------- ---------------------------
1 D: /oracle1/ora81/oradata/test/redo01.log
2 D: /oracle1/ora81/oradata/test/redo02.log
3 D: /oracle1/ora81/oradata/test/redo03.log
1 E: /oracle1/ora81/oradata/test/redo04.log
2 E: /oracle1/ora81/oradata/test/redo05.log
3 E: /oracle1/ora81/oradata/test/redo06.log
5 unknown e: /oracle1/ora81/oradata/test/redo07.log
5 Stale E: /oracle1/ora81/oradata/test/redo08.log
8 rows success
SQL> ALTER SYSTEM SWITCH LOGFILE;
SYSTEM altered
SQL> SELECT * FROM V $ logfile;
Group # status member
---------- --------------------------------------- ----------------------
1 D: /oracle1/ora81/oradata/test/redo01.log2 d: /oracle1/ora81/oradata/test/redo02.log
3 D: /oracle1/ora81/oradata/test/redo03.log
1 E: /oracle1/ora81/oradata/test/redo04.log
2 E: /oracle1/ora81/oradata/test/redo05.log
3 E: /oracle1/ora81/oradata/test/redo06.log
5 E: /oracle1/ora81/oradata/test/redo07.log
5 E: /oracle1/ora81/oradata/test/redo08.log
8 rows success
SQL>
Then, see everything is normal, completely shut down the data (Normal) and perform a cold backup. The relevant part of the log file corruption and repair will be recorded in the alert.log file.
2 If the LGWR cannot access all members of the next group or the damaged log file when the LGWR is changed, the instance is turned off. If you are inactive, then discard and add a new log group to resolve the problem; if activity, the database may need to recover from the online redo log file residue.
SQL> ALTER DATABASE DROP LOGFILE MEMBER 'E: /Oracle1/ora81/oradata/test/redo08.log';
Database altered
SQL> SELECT * FROM V $ logfile;
Group # status member
---------- --------------------------------------- ----------------------------
1 D: /oracle1/ora81/oradata/test/redo01.log
2 D: /oracle1/ora81/oradata/test/redo02.log
3 D: /oracle1/ora81/oradata/test/redo03.log
1 E: /oracle1/ora81/oradata/test/redo04.log
2 E: /oracle1/ora81/oradata/test/redo05.log
3 E: /oracle1/ora81/oradata/test/redo06.log
5 E: /oracle1/ora81/oradata/test/redo07.log
7 rows success
SQL>
In order to simulate a member damage in the log group, we open the text editor and destroy the E: /oracle1/ora81/oradata/test/redo07.log file, then we see:
SQL> ALTER SYSTEM SWITCH LOGFILE;
Alter System Switch logfile
ORA-00313: Unable to open a member of the log group (thread)
SQL> Startup Mount
Oracle routines have been started.
Total System Global Area 36214812 BYtes
Fixed Size 75804 BYTES
Variable size 19283968 bytes
Database buffers 16777216 bytesredo buffers 77824 BYTES
The database is loaded.
SQL> ALTER DATABASE
2 Drop Logfile Group 5;
The database has been changed.
SQL> ALTABASE OPEN;
The database has been changed.
SQL> SELECT * FROM V $ logfile;
Group # status member
---------- --------------------------------------- ----------------
1 D: /oracle1/ora81/oradata/test/redo01.log
2 D: /oracle1/ora81/oradata/test/redo02.log
3 Stale D: /oracle1/ora81/oradata/test/redo03.log
1 E: /oracle1/ora81/oradata/test/redo04.log
2 E: /oracle1/ora81/oradata/test/redo05.log
3 Stale E: /oracle1/ora81/oradata/test/redo06.log
6 rows selected
SQL>
Then, see everything is normal, completely shut down the data (Normal) and perform a cold backup. The relevant part of the log file corruption and repair will be recorded in the alert.log file.
In archiving mode, we need to use Alter Database Clear Logfile Group to recover the database:
SQL> SELECT * FROM V $ logfile;
Group # status member
---------- --------------------------------------- -----------------------
1 D: /oracle1/ora81/oradata/test/redo01.log
2 D: /oracle1/ora81/oradata/test/redo02.log
3 D: /oracle1/ora81/oradata/test/redo03.log
1 E: /oracle1/ora81/oradata/test/redo04.log
2 E: /oracle1/ora81/oradata/test/redo05.log
3 E: /oracle1/ora81/oradata/test/redo06.log
4 f: /oracle1/ora81/oradata/test/redo07.log
4 f: /oracle1/ora81/oradata/test/redo08.log
8 rows success
We use Group4 to test, first determine it is not a current group:
SQL> SELECT Group #, sequence #, bytes, members, status from v $ log;
Group # sequence # bytes Members Status
---------- -------------------------------------- --------
1 440 1048576 2 inactive
2 442 1048576 2 CURRENT
3 439 1048576 2 inactive4 441 1048576 2 inactive
Then let Grup4 have only one MEMBER (DrOP off the remaining member):
SQL> ALTABASE DROP LOGFILE MEMBER 'F: / Oracle1/ora81/oradata/test/redo08.log';
Database altered
SQL> SELECT * FROM V $ logfile;
Group # status member
---------- --------------------------------------- ---------------------------
1 D: /oracle1/ora81/oradata/test/redo01.log
2 D: /oracle1/ora81/oradata/test/redo02.log
3 D: /oracle1/ora81/oradata/test/redo03.log
1 E: /oracle1/ora81/oradata/test/redo04.log
2 E: /oracle1/ora81/oradata/test/redo05.log
3 E: /oracle1/ora81/oradata/test/redo06.log
4 f: /oracle1/ora81/oradata/test/redo07.log
7 rows success
SQL>
Now there is only one member in Group 4, and not current, we damage this group (f: /oracle1/ora81/oradata/test/redo07.log), then, after Switch is in this corrupted group, the database will hang or serious Will Crash, then we may receive the following error:
SQL> ALTER DATABASE CLOGFILE
2 'f: /oracle1/ora81/oradata/test/redo07.log';
Alter Database Clear Logfile
*
Error is located on the first line:
ORA-12571: TNS: Packet Writer Failure
SQL> Conn Intenral @ TEST
Please enter the password:
Error:
ORA-01092: Oracle Instance Terminated. Dischanection Forced
SQL>
At this time, we can quit SQLPLUS and then recover in:
SQL> EXIT
E: /> SQLPLUS INTERNAL
SQL * Plus: Release 8.1.7.0.0 - Production on Monday October 7 23:46:17 2002
(c) CopyRight 2000 Oracle Corporation. All Rights Reserved.
Connected to:
Oracle8i
Enterprise
Edition Release 8.1.7.0.0 - Production
WITH THE PartInog Option
Jserver Release 8.1.7.0.0 - Production
After coming in, the Shutdown Abort database, then, then Startup Mount:
SQL> Shutdown Abortoracle routines have been turned off.
SQL> Startup Mount
Oracle routines have been started.
Total System Global Area 36214812 BYtes
Fixed Size 75804 BYTES
Variable size 19283968 bytes
Database buffers 16777216 BYTES
Redo buffers 77824 bytes
The database is loaded.
It can be seen that the database has no Crash. Now we Clear Group4, first use ALTER DATABASE CLOGFILE Group 4; (if you can't use Alter Database Clear UnarchiveD logfile group 4;):
SQL> ALTER DATABASE CLOGFILE GROUP 4;
The database has been changed.
SQL> ALTABASE OPEN;
The database has been changed.
SQL>
Ok, the database has been recovered:
SQL> SELECT * FROM V $ logfile;
Group # status member
---------- --------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------
1 Stale D: /oracle1/ora81/oradata/test/redo01.log
2 D: /oracle1/ora81/oradata/test/redo02.log
3 D: /oracle1/ora81/oradata/test/redo03.log
1 Stale E: /oracle1/ora81/oradata/test/redo04.log
2 E: /oracle1/ora81/oradata/test/redo05.log
3 E: /oracle1/ora81/oradata/test/redo06.log
4 f: /oracle1/ora81/oradata/test/redo07.log
7 rows success
SQL> SELECT Group #, sequence #, bytes, members, status from v $ log;
Group # sequence # bytes Members Status
---------- -------------------------------------- --------
1 448 1048576 2 inactive
2 446 1048576 2 inactive
3 447 1048576 2 inactive
4 449 1048576 1 CURRENT
SQL> ALTER SYSTEM SWITCH LOGFILE;
SYSTEM altered
SQL> SELECT Group #, sequence #, bytes, members, status from v $ log;
Group # sequence # bytes Members Status
---------- -------------------------------------- --------
1 448 1048576 2 inactive2 450 1048576 2 Current
3 447 1048576 2 inactive
4 449 1048576 1 ACTIVE
SQL>
Related Discussion:
If a transaction starts running, this is to start writing Group 1, no commit and rollback, until current = 5, however, this is archived 2, but 4 is broken, then this is lost, then the data will be lost?
A: No. Because, GROUP 4 is not current, then when Switch is from Group 4 Switch to Group 5, checkpoint has occurred, that is, DBWR has stored data in DataFile, so when the instance is restored, the system checks Group5 (CURRENT), finds that the rollback needs to be rolled, so that the required data is read in the rollback segment.
Then checkpoint occurs when you are from Group 4 Switch to Group 5, and because if the system is slow or the data blocks to be written, CheckPoint has not ended 4 yet, and instance is also Crash. When INSTANCE Recover is Group 4, is this lost data?
A: Obviously. Because when the instance is restored, because there is no checkpoint, the data has not been recorded to the datafile, so the database does not know how to do it, so the database cannot open.
2 If you are writing to all members of the current group, LGWR suddenly cannot access these members, then the database routine is turned off, in which case the database may require media recovery from the online log file residue.
Determine Group4 is current, if not, do Alter System Switch logfile; until Group4 is current:
SQL> SELECT Group #, sequence #, bytes, members, status from v $ log;
Group # sequence # bytes Members Status
---------- -------------------------------------- --------
1 452 1048576 2 inactive
2 450 1048576 2 inactive
3 451 1048576 2 inactive
4 453 1048576 1 CURRENT
SQL> SELECT * FROM V $ logfile;
Group # status member
---------- --------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------
1 D: /oracle1/ora81/oradata/test/redo01.log
2 D: /oracle1/ora81/oradata/test/redo02.log3 d: /oracle1/ora81/oradata/test/redo03.log
1 E: /oracle1/ora81/oradata/test/redo04.log
2 E: /oracle1/ora81/oradata/test/redo05.log
3 E: /oracle1/ora81/oradata/test/redo06.log
4 f: /oracle1/ora81/oradata/test/redo07.log
7 rows success
SQL>
Now destroy Group4. When you find this error:
SQL> ALTABASE OPEN;
ALTER DATABASE OPEN
*
Error is located on the first line:
ORA-00313: ??????? 4 (?? 1)???
ORA-00312: ???? 4 ?? 1: 'f: /oracle1/ora81/oradata/test/redo07.log'
SQL>
So, then shutdown, and mount database:
SQL> Shutdown
ORA-01109: ??????
The database has been uninstalled.
Oracle routines have been closed.
SQL> Startup Mount
Oracle routines have been started.
Total System Global Area 36214812 BYtes
Fixed Size 75804 BYTES
Variable size 19283968 bytes
Database buffers 16777216 BYTES
Redo buffers 77824 bytes
The database is loaded.
Restore the database:
SQL> Recover Database Until Cancel;
Complete media recovery.
SQL> ALTER DATABASE OPEN RESETLOGS;
The database has been changed.
SQL>
SQL> Archive log List
Database log mode archive mode
Automatic archive enable
Archive terminal D: / Oracle1 / Ora81 / ORADATA / TEST / ARCHIVE
The earliest summary information log sequence 1
Next Archive Log Sequence 1
Current log sequence 1
SQL>
The previous Archive is already unavailable, you need Shutdow, then make a Full Backup, then re-Startup database, you can.
