Lost all data files, control files, redo log file (9201) - process method three

zhaozj2021-02-16  98

Lost all data files, control files, redo log file (9201)

Author: David Zhang (Lunar)

Email: moonlunar@163.com

MSN: lunar52@hotmail.com

Three use backup control files (cold backup) recovery

Test data before making cold backup

C: /> SQLPLUS "/ as sysdba"

SQL * Plus: Release 9.2.0.1.0 - Production on Tue Feb 5 23:20:52 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> Archive Log List;

Database log mode archive mode

Automatic archival enabled

Archive Destination E: / Oracle / ORADATA / TEST / Archive

Oldest Online Log Sequence 0

Next log sequence to archive 1

Current Log Sequence 1

SQL> Conn Lunar / Lunar

Connected.

SQL> SELECT *.

No rows selected

SQL>

Cold database

SQL> conn / as sysdba

Connected.

SQL> Archive Log List;

Database log mode archive mode

Automatic archival enabled

Archive Destination E: / Oracle / ORADATA / TEST / Archive

Oldest Online Log Sequence 0

Next log sequence to archive 1

Current Log Sequence 1

SQL> EXIT

Disconnected from 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

Single open a window to make a cold

C: /> d:

D: /> CD D: / clonedb

D: / clonedb> cold_backup.bat> Cold_Backup.log

D: / clonedb>

View the cool log

D: / clonedb> echo beg Cold_Backup Database ...

Begin Cold_Backup Database ...

D: / clonedb> Date / T

2002-02-05 Tuesday

D: / clonedb> Time / T

23:22

D: / clonedb> sqlplus "/ as sysdba" @ shutdown.sqlsql * plus: release 9.2.0.1.0 - Production on Tue Feb 5 23:22:24 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

Connected.

Database closed.

Database dismount.

Oracle Instance Shut Down.

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.

Database opened.

Database closed.

Database dismount.

Oracle Instance Shut Down.

Disconnected from 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

D: / clonedb> Copy E: / Oracle / ORA92 / TEST / *. * D: / clonedb

E: /oracle/ora92/test/cold_backup.bat

E: /oracle/ora92/test/shutdown.sql

E: /oracle/ora92/test/startup.sql

E: /oracle/ora92/test/cold_backup.log

E: /oracle/ora92/test/control03.ctl

E: /oracle/ora92/test/redo01.log

E: /oracle/ora92/test/redo02.log

E: /oracle/ora92/test/redo03.log

E: /oracle/ora92/test/redo04a.log

E: /oracle/ora92/test/redo04b.log

E: /oracle/ora92/test/system01.dbf

E: /oracle/ora92/test/undotbs01.dbf

E: /oracle/ora92/test/drsys01.dbf

E: /oracle/ora92/test/indx01.dbf

E: /oracle/ora92/test/tools01.dbf

E: /oracle/ora92/test/USERS01.DBF

E: /oracle/ora92/test/xdb01.dbf

E: /oracle/ora92/test/rman01.dbf

E: /oracle/ora92/test/control02.ctl

E: /oracle/ora92/test/test_ora_1024.trc

E: /oracle/ora92/test/temp01.dbfe: /oracle/ora92/test/pwdtest.ora

E: /oracle/ora92/test/test_ora_1800.trc

22 files have been copied.

D: / clonedb> Copy E: /oracle/ora92/database/pwdtest.ora d: / clonedb

1 file has been copied.

D: / clonedb> Copy E: / Oracle / ORADATA / TEST / Archive D: / Clones / Archive

E: / Oracle / ORADATA / TEST / ARCHIVE / *

0 files have been copied.

D: / clonedb> sqlplus "/ as sysdba" @ startup.sql

SQL * Plus: Release 9.2.0.1.0 - Production on Tue Feb 5 23:26:37 2002

CopyRight (C) 1982, 2002, Oracle Corporation. All Rights Reserved.

Connected to an iDLE Instance.

Connected to an iDLE Instance.

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.

Database opened.

SYSTEM altered.

Database altered.

SYSTEM altered.

Disconnected from 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

D: / clonedb> Date / T

2002-02-05 Tuesday

D: / clonedb> Time / T

23:26

D: / clonedb> echo the fold backup database success.

The Cold Backup Database Successed.

Test data after making cold backup

Submit some test data (increased after backup)

SQL> Conn Lunar / Lunar

Connected.

SQL> SELECT *.

No rows selected

SQL> INSERT INTO TEST VALUES (25);

1 row created.

SQL> INSERT INTO TEST VALUES (26);

1 row created.

SQL> commit;

COMMIT COMPLETE.

SQL> INSERT INTO TEST VALUES (27);

1 row created.

SQL> SELECT *.

25

26

27

3 rows selected.

SQL>

Open a window to create a checkpoint

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 21:28:47 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> ALTER System Checkpoint;

SYSTEM altered.

SQL>

Go back to the original window and continue to add data

SQL> INSERT INTO TEST VALUES (28);

1 row created.

SQL>

Back to the window of AS Sysdba, Switch logfile

SQL> ALTER SYSTEM SWITCH LOGFILE;

SYSTEM altered.

SQL> Archive Log List;

Database log mode archive mode

Automatic archival enabled

Archive Destination E: / Oracle / ORADATA / TEST / Archive

Oldest Online Log Sequence 1

Next log sequence to archive 2

Current log sequence 2

SQL>

Go back to the original window, after commit, continue to add data

SQL> commit;

COMMIT COMPLETE.

SQL> INSERT INTO TEST VALUES (29);

1 row created.

SQL>

Go back to the window of AS Sysdba, generate checkpoints

SQL> ALTER System Checkpoint;

SYSTEM altered.

SQL>

Go back to the original window, do not contact data without COMMIT

SQL> INSERT INTO TEST VALUES (30);

1 row created.

SQL> SELECT *.

25 (New DataAfter Cold Backup)

26

(commit)

27

(Checkpoint)

Twist

(Switch logfile)

(commit)

29

(Checkpoint)

30

(shutdown abort)

Rows SELECTED.

SQL>

Can only be restored to all commit data before Switch logfile, namely: 26

Back to the AS SYSDBA window, analog database Crash

SQL> Shutdown Abort

Oracle Instance Shut Down.

SQL>

Delete all database files (data files, control files, redo log file)

SQL> Host

Microsoft Windows 2000 [Version 5.00.2195] (C) Copyright 1985-2000 Microsoft Corp.

C: /> DEL E: / Oracle / ORA92 / TEST /*.

E: / Oracle / ORA92 / TEST /*. Is it confirmed (Y / N)? Y

C: /> DIR E: / Oracle / ORA92 / TEST

The volume in the driver E is Oracle

The serial number of the volume is 707A-C50C

E: / Oracle / ORA92 / TEST directory

2002-01-13 21:59

.

2002-01-13 21:59

..

0 file 0 bytes

2 catalogs 2, 802, 413, 568 available bytes

C: /> SQLPLUS "/ as sysdba"

SQL * Plus: Release 9.2.0.1.0 - Production on Tue Feb 5 22:55:33 2002

CopyRight (C) 1982, 2002, Oracle Corporation. All Rights Reserved.

Connected to an iDLE Instance.

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

ORA-00205: Error In Identifying ControlFile, Check Alert log for more info

SQL>

SQL> Shutdown Abort

Oracle Instance Shut Down.

SQL>

Restore database

Cut the nearest cold copy

SQL> Host

Microsoft Windows 2000 [Version 5.00.2195]

(C) All rights reserved 1985-2000 Microsoft Corp.

C: /> COPY D: / ClONEDB /*.* E: / Oracle / ORA92 / TEST

D: /clonedb/cold_backup.bat

D: /clonedb/shutdown.sql

D: /clonedb/startup.sql

D: /clonedb/cold_backup.log

D: /clonedb/control03.ctl

D: /clonedb/redo01.log

D: /clonedb/redo02.log

D: /clonedb/redo03.log

D: /clonedb/redo04a.log

D: /clonedb/redo04b.log

D: /clonedb/system01.dbf

D: /clonedb/undotbs01.dbf

D: /clonedb/drsys01.dbf

D: /clonedb/indx01.dbf

D: /clonedb/tools01.dbf

D: /clonedb/Users01.dbf

D: /clonedb/xdb01.dbf

D: /clonedb/rman01.dbf

D: /clonedb/control02.ctl

D: /clonedb/test_ora_1024.trc

D: /clonedb/temp01.dbf

D: /clonedb/pwdtest.ora

D: /clonedb/test_ora_1800.trc

23 files have been copied. C: /> Del E: / Oracle / ORA92 / TEST / Redo0 *. *

C: /> DIR E: / Oracle / ORA92 / TEST /

The volume in the driver E is Oracle

The serial number of the volume is 707A-C50C

E: / Oracle / ORA92 / TEST directory

2002-01-13 21:59

.

2002-01-13 21:59

..

2002-02-05 22:41 340 cold_backup.bat

2002-02-05 11:28 72 shutdown.sql

2002-02-05 12:19 281 Startup.sql

2002-02-05 22:49 2,949 cold_backup.log

2002-02-05 22:45 1,875,968 Control03.ctl

2002-02-05 22:45 367,009,792 system01.dbf

2002-02-05 22:45 52, 436, 992 Undotbs01.dbf

2002-02-05 22:45 104, 865, 792 Drsys01.dbf

2002-02-05 22:45 31, 465, 472 indx01.dbf

2002-02-05 22:45 31, 465, 472 Tools01.dbf

2002-02-05 22:45 31, 465, 472 users01.dbf

2002-02-05 22:45 52, 436, 992 xdb01.dbf

2002-02-05 22:45 52, 436, 992 rman01.dbf

2002-02-05 22:45 1,875,968 Control02.ctl

2002-02-05 22:07 4,905 Test_ora_1024.trc

2002-02-05 22:27 20,979,712 TEMP01.DBF

2002-02-05 21:49 3,072 PWDTEST.ORA

2002-02-05 22:49 5,416 Test_ora_1800.trc

18 files 748, 331,659 bytes

2 catalog 2,054,361,088 available bytes

C: />

Load but do not open the database

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 20:58:25 2002

CopyRight (C) 1982, 2002, Oracle Corporation. All Rights Reserved.

Connected to an iDLE Instance.

SQL> Startup Nomount

Oracle Instance Started.

Total System Global Area 135338868 BYtes

Fixed size 453492 bytesvariable size 109051904 bytes

Database buffers 25165824 BYTES

Redo buffers 667648 bytes

SQL> ALTABASE MOUNT;

Database altered.

SQL>

Recover the database using the Using Backup ControlFile

SQL> Recover data;

ORA-00283: Recovery Session Canceled Due To ErrorS

ORA-00264: No Recovery Required

SQL>

note

If you copy the cold redo log file, you can open the database (ALTER DATABASE OPEN;), but after the cooling data is lost, that is, the database is open after the database is opened. The procedure is as follows:

SQL> ALTABASE OPEN;

Database altered.

SQL> Archive Log List;

Database log mode archive mode

Automatic archival enabled

Archive Destination E: / Oracle / ORADATA / TEST / Archive

Oldest Online Log Sequence 0

Next log sequence to archive 1

Current Log Sequence 1

SQL> Conn Lunar / Lunar

Connected.

SQL> SELECT *.

No rows selected

SQL>

• If you don't use the use backup controlfile to restore the database. Can't go directly, because there is no Redo Log file (there is no copy of the cold redo log file). The procedure is as follows:

SQL> Recover Database Using Backup ControlFile;

ORA-00279: Change 1089259 generated at 02/05/2002 22:45:48 Needed for thread 1

ORA-00289: Suggestion: E: /Oracle/oradata/test/archive/1_1.arc

ORA-00280: Change 1089259 for Thread 1 is in sequence # 1

Specify log: { = suggested | filename | auto | ca ZENCEL}

Auto

ORA-00279: Change 1089438 generated at 02/05/2002 22:52:46 Needed for thread 1

ORA-00289: Suggestion: E: /Oracle/oradata/test/archive/1_2.arc

ORA-00280: Change 1089438 for Thread 1 Is in Sequence # 2

ORA-00278: log file 'E: /oracle/oradata/test/archive/1_1.arc' no longer needed

For this recovery

ORA-00328: Archived log ends at changing 1088611, need latter change 1089438ra-00334: Archived log: 'E: /oracle/oradata/test/archive/1_2.arc'

SQL>

Recover again using the unsilfile of Until Cancel Useing Backup ControlFile

SQL> ALTER DATABASE OPEN RESETLOGS;

Alter Database Open Resetlogs

*

Error At Line 1:

ORA-01113: File 1 Needs Media Recovery

ORA-01110: Data File 1: 'E: /oracle/ora92/test/system01.dbf'

SQL> Recover Database Until Cancel Using Backup ControlFile;

ORA-00279: Change 1089438 generated at 02/05/2002 22:52:46 Needed for thread 1

ORA-00289: Suggestion: E: /Oracle/oradata/test/archive/1_2.arc

ORA-00280: Change 1089438 for Thread 1 Is in Sequence # 2

Specify log: { = suggested | filename | auto | ca ZENCEL}

Cancel

Media recovery canceled.

SQL>

Please note the status of the update time of the database file at this time:

SQL> Host Dir E: / Oracle / ORA92 / TEST

The volume in the driver E is Oracle

The serial number of the volume is 707A-C50C

E: / Oracle / ORA92 / TEST directory

2002-01-13 21:59

.

2002-01-13 21:59

..

2002-02-05 22:41 340 cold_backup.bat

2002-02-05 11:28 72 shutdown.sql

2002-02-05 12:19 281 Startup.sql

2002-02-05 22:49 2,949 cold_backup.log

2002-02-05 22:45 1,875,968 Control03.ctl

2002-02-05 23:04 367,009,792 system01.dbf

2002-02-05 23:04 52, 436, 992 Undotbs01.dbf

2002-02-05 23:04 104,865,792 drsys01.dbf

2002-02-05 23:04 31, 465, 472 indx01.dbf

2002-02-05 23:04 31, 465, 472 Tools01.dbf

2002-02-05 23:04 31, 465, 472 users01.dbf

2002-02-05 23:04 52, 436, 992 xdb01.dbf

2002-02-05 23:04 52, 436, 992 rman01.dbf

2002-02-05 22:45 1,875,968 Control02.ctl

2002-02-05 22:07 4,905 Test_ora_1024.trc

2002-02-05 22:27 20,979,712 TEMP01.DBF

2002-02-05 21:49 3,072 PWDTEST.ORA

2002-02-05 22:49 5,416 Test_ora_1800.trc

18 files 748, 331,659 bytes

2 catalog 2,054,344,704 available bytes

SQL>

Open database using resetlogs

SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

SQL>

Please note the status of the update time of the database file at this time:

SQL> Host Dir E: / Oracle / ORA92 / TEST

The volume in the driver E is Oracle

The serial number of the volume is 707A-C50C

E: / Oracle / ORA92 / TEST directory

2002-01-13 21:59

.

2002-01-13 21:59

..

2002-02-05 22:41 340 cold_backup.bat

2002-02-05 11:28 72 shutdown.sql

2002-02-05 12:19 281 Startup.sql

2002-02-05 22:49 2,949 cold_backup.log

2002-02-05 22:45 1,875,968 Control03.ctl

2002-02-05 23:06 31, 457, 792 redo01.log

2002-02-05 23:06 31,457,792 redo02.log

2002-02-05 23:06 31, 457, 792 redo03.log

2002-02-05 23:06 1,049,088 redo04a.log

2002-02-05 23:06 1,049,088 redo04b.log

2002-02-05 23:06 367,009,792 System01.dbf

2002-02-05 23:06 52, 436, 992 undotbs01.dbf

2002-02-05 23:06 104, 865, 792 drsys01.dbf

2002-02-05 23:06 31, 465, 472 indx01.dbf

2002-02-05 23:06 31, 465, 472 Tools01.dbf

2002-02-05 23:06 31, 465, 472 users01.dbf

2002-02-05 23:06 52, 436, 992 xdb01.dbf

2002-02-05 23:06 52, 436, 992 rman01.dbf

2002-02-05 22:45 1,875,968 Control02.ctl

2002-02-05 22:07 4,905 Test_ora_1024.trc

2002-02-05 22:27 20,979,712 TEMP01.DBF

2002-02-05 21:49 3,072 Pwdtest.ora2002-02-05 22:49 5,416 test_ora_1800.trc

23 files 844, 803, 211 bytes

2 catalogs 1,957,855,232 available bytes

SQL>

Verify recovery

SQL> Archive Log List;

Database log mode archive mode

Automatic archival enabled

Archive Destination E: / Oracle / ORADATA / TEST / Archive

Oldest Online Log Sequence 0

Next log sequence to archive 1

Current Log Sequence 1

SQL> Conn Lunar / Lunar

Connected.

SQL> SELECT *.

Fly

------------

25

26

SQL>

Lost some data commit data, because those data are in REDO, yet not filed

转载请注明原文地址:https://www.9cbs.com/read-11454.html

New Post(0)