How to realize backup and recovery in RMAN 03/28 Update SLTechnology News&Howtos

How to realize backup and recovery in RMAN

2025-03-28 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)05/31 Report--

Editor to share with you how to achieve RMAN backup and recovery, I hope you will learn something after reading this article, let's discuss it together!

# # #

# [1] Control file corruption (rman and regular recovery) #

# # #

Preparatory work

Backup

RMAN > configure controlfile autobackup on

New RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP ON

New RMAN configuration parameters have been successfully stored

RMAN > configure controlfile autobackup format for device type disk to'c:\ temp\%

New RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO'c:\ temp\% F'

New RMAN configuration parameters have been successfully stored

RMAN >

RMAN > backup database

Launch backup in 25-February-05

Assigned channel: ORA_DISK_1

Channel ORA_DISK_1: sid=12 devtype=DISK

Assigned channel: ORA_DISK_2

Channel ORA_DISK_2: sid=13 devtype=DISK

Channel ORA_DISK_1: starting backup set of full data files

Channel ORA_DISK_1: specifying data files in the backup set

Enter the data file fno=00002 name=C:\ ORACLE\ ORADATA\ PDF\ UNDOTBS01.DBF

Enter the data file fno=00003 name=C:\ ORACLE\ ORADATA\ PDF\ INDX01.DBF

Enter the data file fno=00005 name=C:\ ORACLE\ ORADATA\ PDF\ USERS01.DBF

Channel ORA_DISK_1: starting segment 1 from 25-February-05

Channel ORA_DISK_2: starting backup set of full data files

Channel ORA_DISK_2: specifying data files in the backup set

Enter the data file fno=00001 name=C:\ ORACLE\ ORADATA\ PDF\ SYSTEM01.DBF

Enter the data file fno=00004 name=C:\ ORACLE\ ORADATA\ PDF\ TOOLS01.DBF

Channel ORA_DISK_2: starting segment 1 from 25-February-05

Channel ORA_DISK_1: completed segment 1 from 25 to February to 05

Segment handle=C:\ ORACLE\ ORA92\ DATABASE\ 07GDKT8C_1_1 comment=NONE

Channel ORA_DISK_1: backup set completed, elapsed time: 00:00:56

Channel ORA_DISK_2: completed segment 1 from 25 to February to 05

Segment handle=C:\ ORACLE\ ORA92\ DATABASE\ 08GDKT8C_1_1 comment=NONE

Channel ORA_DISK_2: backup set completed, elapsed time: 00:00:57

Completion of backup in 25-February-05

Launch Control File and SPFILE Autobackup in 25-February-05

Paragraph handle=C:\ TEMP\ Cmur3171861129-20050225-00 comment=NONE

Completion of Control File and SPFILE Autobackup in 25-February-05

RMAN >

Regular backup

Backup data file cp C:\ ORACLE\ ORADATA\ PDF\ * C:\ ORACLE\ ORADATA\ bak

Backup control file

# # #

# [1.1] there are old backup data backup and control files trace

# # #

SQL > alter session set tracefile_identifier=fanglf

The session has changed.

SQL > alter database backup controlfile to trace

The database has changed.

SQL > alter database backup controlfile to'c:\ temp\ controlfile.bak'

The database has changed.

~ ~ ~

We can also use RMAN to copy backup control files

RMAN > copy CURRENT CONTROLFILE TO'c:\ cf.cpy'

Launch copy in 03-March-05

Assigned channel: ORA_DISK_1

Channel ORA_DISK_1: sid=16 devtype=DISK

Channel ORA_DISK_1: current control file copied

Output file name = C:\ CF.CPY

Completion of copy in 03-March-05

~ ~ ~

SQL > col name format A50

SQL > select * from v$controlfile

STATUS NAME

C:\ ORACLE\ ORADATA\ PDF\ CONTROL01.CTL

C:\ ORACLE\ ORADATA\ PDF\ CONTROL02.CTL

C:\ ORACLE\ ORADATA\ PDF\ CONTROL03.CTL

SQL > create table test (an int,v varchar)

Use ULTRAEDIT editing to clear most of the CONTROL01.CTL data

SQL > select * from vested control file; shutdown abort

The ORACLE routine has been closed.

SQL > startup nomount

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

SQL >

SQL > CREATE CONTROLFILE REUSE DATABASE "DF" NORESETLOGS ARCHIVELOG

2-- SET STANDBY TO MAXIMIZE PERFORMANCE

3 MAXLOGFILES 5

4 MAXLOGMEMBERS 3

5 MAXDATAFILES 100

6 MAXINSTANCES 1

7 MAXLOGHISTORY 226

8 LOGFILE

9 GROUP 1'C:\ ORACLE\ ORADATA\ PDF\ REDO01.LOG' SIZE 100m

10 GROUP 2'C:\ ORACLE\ ORADATA\ PDF\ REDO02.LOG' SIZE 100m

11 GROUP 3'C:\ ORACLE\ ORADATA\ PDF\ REDO03.LOG' SIZE 100m

12-STANDBY LOGFILE

13 DATAFILE

14'C:\ ORACLE\ ORADATA\ PDF\ SYSTEM01.DBF'

15'C:\ ORACLE\ ORADATA\ PDF\ UNDOTBS01.DBF'

16'C:\ ORACLE\ ORADATA\ PDF\ INDX01.DBF'

17'C:\ ORACLE\ ORADATA\ PDF\ TOOLS01.DBF'

18'C:\ ORACLE\ ORADATA\ PDF\ USERS01.DBF'

19 CHARACTER SET ZHS16GBK

Control file has been created

SQL > alter database open

Alter database open

ERROR is on line 1:

ORA-01113:?? 1?

ORA-01110: 1:'C:\ ORACLE\ ORADATA\ PDF\ SYSTEM01.DBF'

SQL > startup

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

The database is loaded.

ORA-01113: file 1 requires media recovery

ORA-01110: data file 1:'C:\ ORACLE\ ORADATA\ PDF\ SYSTEM01.DBF'

SQL > recover database;-- because shutdown abort is closed, it needs to be restored.

Complete the media recovery.

SQL > alter database open

The database has changed.

# # #

# [Test 2]-use trace to restore normal shutdown

# # #

SQL > insert into test select rownum,'aaaaaaaaaaaaaaaaaaaaaa' from dba_objects

6165 rows were created.

SQL > /

6165 rows were created.

SQL > insert into test select * from test

12330 rows were created.

SQL > commit

The submission is complete.

SQL > shutdown

The database has been closed.

The database has been uninstalled.

The ORACLE routine has been closed.

SQL > startup nomount

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

SQL >

SQL > CREATE CONTROLFILE REUSE DATABASE "DF" NORESETLOGS ARCHIVELOG

2-- SET STANDBY TO MAXIMIZE PERFORMANCE

3 MAXLOGFILES 5

4 MAXLOGMEMBERS 3

5 MAXDATAFILES 100

6 MAXINSTANCES 1

7 MAXLOGHISTORY 226

8 LOGFILE

9 GROUP 1'C:\ ORACLE\ ORADATA\ PDF\ REDO01.LOG' SIZE 100m

10 GROUP 2'C:\ ORACLE\ ORADATA\ PDF\ REDO02.LOG' SIZE 100m

11 GROUP 3'C:\ ORACLE\ ORADATA\ PDF\ REDO03.LOG' SIZE 100m

12-STANDBY LOGFILE

13 DATAFILE

14'C:\ ORACLE\ ORADATA\ PDF\ SYSTEM01.DBF'

15'C:\ ORACLE\ ORADATA\ PDF\ UNDOTBS01.DBF'

16'C:\ ORACLE\ ORADATA\ PDF\ INDX01.DBF'

17'C:\ ORACLE\ ORADATA\ PDF\ TOOLS01.DBF'

18'C:\ ORACLE\ ORADATA\ PDF\ USERS01.DBF'

19 CHARACTER SET ZHS16GBK

Control file has been created

SQL > alter database open

The database has changed.

~ ~

We see that if it is a normal database downmachine

So after creating a control file using noresetlog

You can open the database directly

~ ~

SQL > select count (*) from test

COUNT (*)

24660

Complete recovery of database with trace

SQL > select * from v$tempfile

No rows selected

SQL > select name from v$tablespace

NAME

SYSTEM

UNDOTBS1

INDX

TOOLS

USERS

TEMP

~ ~

Notice that we can see that due to the reconstruction of the control file

Temporary files for temporary tablespaces are gone

~ ~

# # #

# [Test 3]-use control file backup to restore

# # #

Restore backup control files

C:\ Documents and Settings\ lifeng.fang > copy c:\ temp\ controlfile.bak

C:\ oracle\ oradata\ pdf\ CONTROL01.CTL

1 file has been copied.

C:\ Documents and Settings\ lifeng.fang > copy c:\ temp\ controlfile.bak

C:\ oracle\ oradata\ pdf\ CONTROL02.CTL

1 file has been copied.

C:\ Documents and Settings\ lifeng.fang > copy c:\ temp\ controlfile.bak

C:\ oracle\ oradata\ pdf\ CONTROL03.CTL

1 file has been copied.

~ ~

Backup control files do not store information about online log files

And data files end the SCN, so it will not be used on restore

Use the online log file, and therefore assume that the data file

An infinite end SCN, so to recuperate him, you must

Resetlogs, but we can see it in the example [Test 5.1]

We can force the application of online logs for full recovery

~ ~

When the database is closed with normal or immediate, send

Out of checkpoint processing, which will be controlled for each data file

The corresponding ending SCN is set in the file to make it equal to the data

The corresponding start SCN in the header

~ ~

SQL > startup

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

ORA-01991:?'C:\ oracle\ ora92\ DATABASE\ PWDpdf.ORA'

SQL > recover database

ORA-00283:?

ORA-01610:?? BACKUP CONTROLFILE?

~ ~

The original checkpoint counter in the header of the data file will never be greater than

Currently controls the checkpoint counter in the file, so it must be in the

Specify USING BACKUP CONTROLFILE on restore

This will be based on the backup control file information

~ ~

SQL > alter database open resetlogs

Alter database open resetlogs

ERROR is on line 1:

ORA-01152:?? 1?

ORA-01110: 1:'C:\ ORACLE\ ORADATA\ PDF\ SYSTEM01.DBF'

~ ~

At this point, there is no recovery, and the current data file header is checked

The inconsistency between the start SCN of and the end SCN of the control file requires

Perform a recovery

~ ~

The database cannot be restored here

However, the first method is successfully implemented.

# # #

# [Test 4] We now simulate backup control files, while backing up some data files prior to backup

# data files of control files

# # #

SQL > shutdown immediate

The database has been closed.

The database has been uninstalled.

Copy SYSTEM01.DBF copy SYSTEM01.DBF

Alter database backup controlfile to'c:\ temp\ controlfile1.bak'

SQL > shutdown immediate

The database has been closed.

The database has been uninstalled.

C:\ Documents and Settings\ lifeng.fang > copy c:\ temp\ controlfile1.bak C:\ oracle\ or

Adata\ pdf\ CONTROL01.CTL

1 file has been copied.

C:\ Documents and Settings\ lifeng.fang > copy c:\ temp\ controlfile1.bak C:\ oracle\ or

Adata\ pdf\ CONTROL02.CTL

1 file has been copied.

C:\ Documents and Settings\ lifeng.fang > copy c:\ temp\ controlfile1.bak C:\ oracle\ or

Adata\ pdf\ CONTROL03.CTL

1 file has been copied.

SQL > startup

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

The database is loaded.

ORA-01589: to open the database, you must use the RESETLOGS or NORESETLOGS option

SQL > startup mount

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

The database is loaded.

SQL > recover database using backup controlfile

ORA-00279: a change of 1069041 (generated at 15:36:00 at 02Accord 25Accord 2005) is required for thread 1

ORA-00289: suggestion: C:\ ORACLE\ ORA92\ RDBMS\ ARC00012.001

ORA-00280: change 1069041 is made in sequence # 12 for thread 1

Specify log: {= suggested | filename | AUTO | CANCEL}

Auto

ORA-00308: unable to open archive log'C:\ ORACLE\ ORA92\ RDBMS\ ARC00012.001'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) the system cannot find the specified file.

ORA-00308: unable to open archive log'C:\ ORACLE\ ORA92\ RDBMS\ ARC00012.001'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) the system cannot find the specified file.

SQL > alter database open

Alter database open

ERROR is on line 1:

ORA-01589: to open the database, you must use the RESETLOGS or NORESETLOGS option

SQL > alter database open resetlogs

Alter database open resetlogs

ERROR is on line 1:

ORA-01113: file 1 requires media recovery

ORA-01110: data file 1:'C:\ ORACLE\ ORADATA\ PDF\ SYSTEM01.DBF'

SQL > recover database using backup controlfile until cancel

ORA-00279: a change of 1069041 (generated at 15:36:00 at 02Accord 25Accord 2005) is required for thread 1

ORA-00289: suggestion: C:\ ORACLE\ ORA92\ RDBMS\ ARC00012.001

ORA-00280: change 1069041 is made in sequence # 12 for thread 1

Specify log: {= suggested | filename | AUTO | CANCEL}

Cancel

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS will have the following error

ORA-01152: file 2 was not restored from a complete old backup

ORA-01110: data file 2:'C:\ ORACLE\ ORADATA\ PDF\ UNDOTBS01.DBF'

ORA-01112: media recovery is not started

SQL > alter database open resetlogs

Alter database open resetlogs

ERROR is on line 1:

ORA-01152: file 2 was not restored from a complete old backup

ORA-01110: data file 2:'C:\ ORACLE\ ORADATA\ PDF\ UNDOTBS01.DBF'

~ ~

Although the SYSTEM file was backed up before backing up the control file, the

Other files are not backed up, but there is more than the control file checkpoint SCN

The information can not be recovered successfully

If there is a read-only tablespace, use backup to control file recovery

To offline first, see backup and recovery manual

For restoring using backup control files because the default is all

Data files are online, and you need to write data when you resetlogs

File, then when the read-only file is restored, because the read-only file is not

If you need to recover, you have to force him to offline.

Therefore, you should also pay attention when selecting the control file, if you select when

When it happens to be a control file in a read-only state, the data text must be

Offline, so that the number is read-write before switching

It can't be recovered, and vice versa.

~ ~

# # #

# [Test 5] We now simulate the corruption of the control files and back up all the data files before backing up the control files

# # #

Copy SYSTEM01.DBF copy SYSTEM01.DBF

Copy UNDOTBS01.DBF copy UNDOTBS01.DBF

Copy TOOLS01.DBF copy TOOLS01.DBF

Copy USERS01.DBF copy USERS01.DBF

Copy INDX01.DBF copy INDX01.DBF

SQL > alter database backup controlfile to'c:\ temp\ controlfile2.bak'

The database has changed.

Replace the old control file

SQL > insert into test select * from test

24660 rows were created.

SQL > commit

The submission is complete.

SQL > select count (*) from test

COUNT (*)

49320

SQL > shutdown

The database has been closed.

The database has been uninstalled.

The ORACLE routine has been closed.

Copy c:\ temp\ controlfile2.bak C:\ oracle\ oradata\ pdf\ CONTROL01.CTL

Copy c:\ temp\ controlfile2.bak C:\ oracle\ oradata\ pdf\ CONTROL02.CTL

Copy c:\ temp\ controlfile2.bak C:\ oracle\ oradata\ pdf\ CONTROL03.CTL

Rename SYSTEM01.DBF copy SYSTEM01.DBF SYSTEM01.DBF-- > temporary files may not be used

Rename UNDOTBS01.DBF copy UNDOTBS01.DBF UNDOTBS01.DBF

Rename TOOLS01.DBF copy TOOLS01.DBF TOOLS01.DBF

Rename USERS01.DBF copy USERS01.DBF USERS01.DBF

Rename INDX01.DBF copy INDX01.DBF INDX01.DBF

SQL > startup mount

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

The database is loaded.

SQL > recover database using backup controlfile until cancel

ORA-00279: a change of 1069386 (generated at 15:47:59 at 02Accord 25Accord 2005) is required for thread 1

ORA-00289: suggestion: C:\ ORACLE\ ORA92\ RDBMS\ ARC00013.001

ORA-00280: change 1069386 is made in sequence # 13 for thread 1

Specify log: {= suggested | filename | AUTO | CANCEL}

Auto-- > > recovery using auto is also successful

ORA-00308: unable to open archive log'C:\ ORACLE\ ORA92\ RDBMS\ ARC00013.001'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) the system cannot find the specified file.

ORA-00308: unable to open archive log'C:\ ORACLE\ ORA92\ RDBMS\ ARC00013.001'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) the system cannot find the specified file.

SQL > recover database using backup controlfile until cancel;-- > this cancel indicates that every time a log is applied, it will be prompted until it ends after typing cancel

ORA-00279: a change of 1069386 (generated at 15:47:59 at 02Accord 25Accord 2005) is required for thread 1

ORA-00289: suggestion: C:\ ORACLE\ ORA92\ RDBMS\ ARC00013.001

ORA-00280: change 1069386 is made in sequence # 13 for thread 1

~ ~

Cancel is usually better than change and time recovery

~ ~

Specify log: {= suggested | filename | AUTO | CANCEL}

Cancel-- enter cancel to restore successfully-- > > cancel option allows you to roll forward one log file at a time, and enter cancel when you need to stop the recovery

In this way, online log files are not automatically used.

Media recovery has been cancelled.

SQL > alter database open resetlogs

The database has changed.

SQL > select CHECKPOINT_CHANGE# from v$database

CHECKPOINT_CHANGE#

1069388

SQL > select count (*) from test

COUNT (*)

24660-lost data

~ ~ ~

Then we use backup to control file recovery, is it certain that we can not restore the data in the online log?

The answer is wrong, in fact, it can also be fully recovered (of course, we are better off using trace)

~ ~ ~

# # #

# [Test 5.1] We now simulate control file corruption, control file backup, all data file backups prior to control file backup

# using online logs for full recovery

# # #

SQL > startup

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

The database is loaded.

The database is already open.

SQL > create table arch (status varchar (2)) tablespace users

The table has been created.

SQL > alter system switch logfile

The system has changed.

SQL > insert into arch select 'ok' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

SQL > insert into arch select * from arch

6166 rows were created.

SQL > /

12332 rows were created.

SQL > /

24664 rows were created.

SQL > /

49328 rows were created.

SQL > commit

The submission is complete.

SQL > alter system switch logfile

The system has changed.

SQL > insert into arch select 'no' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

SQL > select GROUP#,SEQUENCE#,archived, STATUS from v$log

GROUP# SEQUENCE# ARC STATUS

1 5 NO CURRENT

2 3 YES ACTIVE

3 4 YES ACTIVE

SQL > select resetlogs_change#-1 control FILECTROLFILER CHANGE # from v$database

RESETLOGS_CHANGE#-1 CONTROLFILE_CHANGE#

68658 69410

SQL > shutdown

The database has been closed.

The database has been uninstalled.

The ORACLE routine has been closed.

Restore backup control files, restore all backup data files

SQL > startup mount

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

The database is loaded.

SQL > alter database open

Alter database open

ERROR is on line 1:

ORA-00314: log 1 (thread 1), expected sequence number does not match

ORA-00312: online log 1 thread 1:'C:\ ORACLE\ ORADATA\ TESTDB\ REDO01.LOG'

SQL > recover database using backup controlfile until cancel

ORA-00279: a change of 69257 (generated at 11:08:18 at 02Universe 28 ORA-00279) is required for thread 1

ORA-00289: suggestion: C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_3.DBF

ORA-00280: change 69257 is made in sequence # 3 for thread 1

Specify log: {= suggested | filename | AUTO | CANCEL}

Cancel

Media recovery has been cancelled.

SQL > select resetlogs_change#-1 control FILECTROLFILER CHANGE # from v$database

RESETLOGS_CHANGE#-1 CONTROLFILE_CHANGE#

68658 69257

SQL > recover database using backup controlfile until cancel

ORA-00279: a change of 69257 (generated at 11:08:18 at 02Universe 28 ORA-00279) is required for thread 1

ORA-00289: suggestion: C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_3.DBF

ORA-00280: change 69257 is made in sequence # 3 for thread 1

Specify log: {= suggested | filename | AUTO | CANCEL}

ORA-00279: a change of 69362 (generated at 11:21:25 on 03amp 04Compact) is required for thread 1

ORA-00289: suggestion: C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_4.DBF

ORA-00280: change 69362 is made in sequence # 4 for thread 1

ORA-00278: the log file'C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE is no longer required for this restore

Specify log: {= suggested | filename | AUTO | CANCEL}

ORA-00279: a change of 69408 (generated at 11:21:26 on 03amp 04Compact) is required for thread 1

ORA-00289: suggestion: C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_5.DBF

ORA-00280: change 69408 is made in sequence # 5 for thread 1

ORA-00278: log file'C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1room4.DBF' is no longer required for this restore

Specify log: {= suggested | filename | AUTO | CANCEL}

ORA-00308: unable to open archive log'C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1' 5.DBF'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) the system cannot find the specified file.

SQL > select resetlogs_change#-1 control FILECTROLFILER CHANGE # from v$database

RESETLOGS_CHANGE#-1 CONTROLFILE_CHANGE#

68658 69408

So far, it is not a complete recovery.

So can we just...

What about applying online logs?

Let's use SEQUENCE=3 and 4 first.

Log C:\ oracle\ oradata\ testdb\ REDO02.LOG

C:\ oracle\ oradata\ testdb\ REDO03.LOG

We can see that this file is not needed for recovery

SQL > recover database using backup controlfile until cancel

ORA-00279: a change of 69408 (generated at 11:21:26 on 03amp 04Compact) is required for thread 1

ORA-00289: suggestion: C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_5.DBF

ORA-00280: change 69408 is made in sequence # 5 for thread 1

Specify log: {= suggested | filename | AUTO | CANCEL}

C:\ oracle\ oradata\ testdb\ REDO02.LOG

ORA-00310: archive log contains sequence 3; requirement sequence 5

ORA-00334: archive log:'C:\ ORACLE\ ORADATA\ TESTDB\ REDO02.LOG'

SQL > recover database using backup controlfile until cancel

ORA-00279: a change of 69408 (generated at 11:21:26 on 03amp 04Compact) is required for thread 1

ORA-00289: suggestion: C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_5.DBF

ORA-00280: change 69408 is made in sequence # 5 for thread 1

Specify log: {= suggested | filename | AUTO | CANCEL}

C:\ oracle\ oradata\ testdb\ REDO03.LOG

ORA-00310: archive log contains sequence 4; requirement sequence 5

ORA-00334: archive log:'C:\ ORACLE\ ORADATA\ TESTDB\ REDO03.LOG'

SQL > select resetlogs_change#-1 control FILECTROLFILER CHANGE # from v$database

RESETLOGS_CHANGE#-1 CONTROLFILE_CHANGE#

68658 69408

~ ~

We see that inactive online logs are not applied

We use SEQUENCE=5

Log C:\ oracle\ oradata\ testdb\ REDO01.LOG

We can see that this file is required for recovery

SQL > recover database using backup controlfile until cancel

ORA-00279: a change of 69408 (generated at 11:21:26 on 03amp 04Compact) is required for thread 1

ORA-00289: suggestion: C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_5.DBF

ORA-00280: change 69408 is made in sequence # 5 for thread 1

Specify log: {= suggested | filename | AUTO | CANCEL}

C:\ oracle\ oradata\ testdb\ REDO01.LOG-this is the key step

Applied logs.

Complete the media recovery.

SQL > select resetlogs_change#-1 control FILECTROLFILER CHANGE # from v$database

RESETLOGS_CHANGE#-1 CONTROLFILE_CHANGE#

68658 69418-SCN is applied in SEQUENCE=5

SQL > alter database open resetlogs

The database has changed.

SQL > select resetlogs_change#-1 control FILECTROLFILER CHANGE # from v$database

RESETLOGS_CHANGE#-1 CONTROLFILE_CHANGE#

69418 69516

SQL > select count (*), status from arch group by status

COUNT (*) ST

6166 no

98656 ok

-- > > full recovery

~ ~

This is mainly due to the application of online activity logs.

Recovery of C:\ oracle\ oradata\ testdb\ REDO01.LOG

~ ~

# # #

# [Test 6] Let's take a look. There are old data files backed up and backup control files inserted.

# can the incoming data be recovered after shutdown abort

# # #

SQL > startup

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

The database is loaded.

The database is already open.

SQL > create table arch (status varchar (2)) tablespace users

The table has been created.

SQL > alter system switch logfile

The system has changed.

SQL > insert into arch select 'ok' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

SQL > insert into arch select * from arch

6166 rows were created.

SQL > /

12332 rows were created.

SQL > /

24664 rows were created.

SQL > /

49328 rows were created.

SQL > commit

The submission is complete.

SQL > alter system switch logfile

The system has changed.

SQL > select resetlogs_change#-1 control FILECTROLFILER CHANGE # from v$database

RESETLOGS_CHANGE#-1 CONTROLFILE_CHANGE#

68658 69611

SQL >

SQL > insert into arch select 'no' from dba_objects

6166 rows were created.

SQL > select resetlogs_change#-1 control FILECTROLFILER CHANGE # from v$database

RESETLOGS_CHANGE#-1 CONTROLFILE_CHANGE#

68658 69611

SQL > commit

The submission is complete.

SQL > select resetlogs_change#-1 control FILECTROLFILER CHANGE # from v$database

RESETLOGS_CHANGE#-1 CONTROLFILE_CHANGE#

68658 69611

SQL > shutdown abort

The ORACLE routine has been closed.

SQL > exit

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 disconnected

C:\ Documents and Settings\ lifeng.fang > sqlplus "sys/sunsdl as sysdba"

SQL*Plus: Release 9.2.0.1.0-Production on Wednesday March 2 14:25:25 2005

Connected to an idle routine.

SQL > startup mount

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

The database is loaded.

SQL > select resetlogs_change#-1 control FILECTROLFILER CHANGE # from v$database

RESETLOGS_CHANGE#-1 CONTROLFILE_CHANGE#

68658 69360

SQL > recover database using backup controlfile

ORA-00279: a change of 69456 (generated at 10:26:30 in 03Universe 02) is required for thread 1

ORA-00289: suggestion: C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_3.DBF

ORA-00280: change 69456 is made in sequence # 3 for thread 1

Specify log: {= suggested | filename | AUTO | CANCEL}

ORA-00279: a change of 69564 (generated at 14:23:19 in 03Universe 02) is required for thread 1

ORA-00289: suggestion: C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_4.DBF

ORA-00280: change 69564 is made in sequence # 4 for thread 1

ORA-00278: the log file'C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_3.DBF is no longer required for this restore

Specify log: {= suggested | filename | AUTO | CANCEL}

ORA-00279: a change of 69610 (generated at 14:23:21 in 03Universe 02) is required for thread 1

ORA-00289: suggestion: C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_5.DBF

ORA-00280: change 69610 is made in sequence # 5 for thread 1

ORA-00278: the log file'C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_4.DBF is no longer required for this restore

Specify log: {= suggested | filename | AUTO | CANCEL}

ORA-00308: unable to open archive log'C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1' 5.DBF'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) the system cannot find the specified file.

SQL > select resetlogs_change#-1 control FILECTROLFILER CHANGE # from v$database

RESETLOGS_CHANGE#-1 CONTROLFILE_CHANGE#

68658 69610

SQL > recover database using backup controlfile until change 69620;-- We see that the CONTROLFILE_CHANGE# is 69610, so I'll randomly increase this value by 69620 to see if it can be restored to after 69610.

ORA-00279: a change of 69610 (generated at 14:23:21 in 03Universe 02) is required for thread 1

ORA-00289: suggestion: C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_5.DBF

ORA-00280: change 69610 is made in sequence # 5 for thread 1

Specify log: {= suggested | filename | AUTO | CANCEL}

ORA-00308: unable to open archive log'C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1' 5.DBF'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) the system cannot find the specified file.

SQL > select resetlogs_change#-1 control FILECTROLFILER CHANGE # from v$database

RESETLOGS_CHANGE#-1 CONTROLFILE_CHANGE#

68658 69610

SQL > recover database using backup controlfile until change 69620

ORA-00279: a change of 69610 (generated at 14:23:21 in 03Universe 02) is required for thread 1

ORA-00289: suggestion: C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_5.DBF

ORA-00280: change 69610 is made in sequence # 5 for thread 1

Specify log: {= suggested | filename | AUTO | CANCEL}

ORA-00308: unable to open archive log'C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1' 5.DBF'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) the system cannot find the specified file.

SQL > select resetlogs_change#-1 control FILECTROLFILER CHANGE # from v$database

RESETLOGS_CHANGE#-1 CONTROLFILE_CHANGE#

68658 69610

Here, if there is no online log,

If it is damaged, it can be applied online

Log full recovery

SQL > alter database open resetlogs

The database has changed.

SQL > select count (*), status from arch group by status

COUNT (*) ST

98656 ok-- > > cannot restore status = no, which is in the online log, but until cancel+ backup controlfile does not roll forward the online log file

The 5.1 method can also be used after testing.

Apply online logs for full recovery

SQL > select resetlogs_change#-1 control FILECTROLFILER CHANGE # from v$database

RESETLOGS_CHANGE#-1 CONTROLFILE_CHANGE#-- > > Strange that the value here becomes the value I want to until change.

69610 69720

SQL > select count (*), status from arch group by status

COUNT (*) ST

98656 ok

[Last edited by fly115 on 2005-3-21 at 19:37]

UID393 post 3566 essence 23 points 3803 circulation points 3803 points money 0 yuan reading rights 225online time 173hours registration time 2003-1-21 finally log in 2008-8-27 to view details

TOP

Fly115

Super moderator

Send a short message in personal space and add it as a friend. Currently, students on the third floor are published on 2005-3-19 17:58 only to see the author.

# # #

# [Test 7] Let's take a look. There are old data files backed up and backup control files inserted.

# whether the incoming data can be recovered after normal shutdown

# # #

The test results are the same as above.

Full recovery can only be achieved by create controlfile method.

You can also refer to [Test 5.1]

~ ~

We noticed that resetlogs can still be used here.

Before the trace to control the file recovery, because of the structure

There is no change.

~ ~

# # #

# [Test 8] Let's see, there are old data files backed up and plugged in

# the incoming data will be used create controlfile noresetlogs after normal shutdown

# method to restore the control file, can it be restored

# # #

SQL > STARTUP

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

The database is loaded.

The database is already open.

SQL > create table arch (status varchar (2)) tablespace users

The table has been created.

SQL > alter system switch logfile

The system has changed.

SQL > insert into arch select 'ok' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

SQL > insert into arch select * from arch

6166 rows were created.

SQL > /

12332 rows were created.

SQL > /

24664 rows were created.

SQL > /

49328 rows were created.

SQL > commit

The submission is complete.

SQL > alter system switch logfile

The system has changed.

SQL > select resetlogs_change#-1 control FILECTROLFILER CHANGE # from v$database

RESETLOGS_CHANGE#-1 CONTROLFILE_CHANGE#

68658 69607

SQL > insert into arch select 'no' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

SQL > shutdown

The database has been closed.

The database has been uninstalled.

The ORACLE routine has been closed.

Delete control file

Delete USERS01.DBF

SQL > STARTUP NOMOUNT

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

SQL > CREATE CONTROLFILE REUSE DATABASE "TESTDB" NORESETLOGS ARCHIVELOG

2-- SET STANDBY TO MAXIMIZE PERFORMANCE

3 MAXLOGFILES 5

4 MAXLOGMEMBERS 3

5 MAXDATAFILES 100

6 MAXINSTANCES 1

7 MAXLOGHISTORY 226

8 LOGFILE

9 GROUP 1'C:\ ORACLE\ ORADATA\ TESTDB\ REDO01.LOG' SIZE 100m

10 GROUP 2'C:\ ORACLE\ ORADATA\ TESTDB\ REDO02.LOG' SIZE 100m

11 GROUP 3'C:\ ORACLE\ ORADATA\ TESTDB\ REDO03.LOG' SIZE 100m

12-STANDBY LOGFILE

13 DATAFILE

14'C:\ ORACLE\ ORADATA\ TESTDB\ SYSTEM01.DBF'

15'C:\ ORACLE\ ORADATA\ TESTDB\ UNDOTBS01.DBF'

16'C:\ ORACLE\ ORADATA\ TESTDB\ INDX01.DBF'

17'C:\ ORACLE\ ORADATA\ TESTDB\ TOOLS01.DBF'

18'C:\ ORACLE\ ORADATA\ TESTDB\ USERS01.DBF'

19 CHARACTER SET ZHS16GBK

Control file has been created

SQL > ALTER DATABASE OPEN

ALTER DATABASE OPEN

ERROR is on line 1:

ORA-01113:?? 5?

ORA-01110: 5:'C:\ ORACLE\ ORADATA\ TESTDB\ USERS01.DBF'

SQL > RECOVER DATABASE

Complete the media recovery.

SQL > ALTER DATABASE OPEN

The database has changed.

SQL > select count (*), status from arch group by status

COUNT (*) ST

6166 no

98656 ok

Fully recovered.

# # #

# [1.2] autobackup,catalog exception with RMAN

# # #

SQL > startup nomount

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

SQL > exit

C:\ Documents and Settings\ lifeng.fang > rman nocatalog target "sys/sunsdl"

Recovery Manager: version 9.2.0.1.0-Production

Connect to the target database: pdf (not installed)

Replacing recovery directory with target database control file

RMAN > restore controlfile from autobackup

Launch restore in 25-February-05

Assigned channel: ORA_DISK_1

Channel ORA_DISK_1: sid=13 devtype=DISK

RMAN-00571: =

RMAN-00569: = ERROR MESSAGE STACK FOLLOWS =

RMAN-00571: =

RMAN-03002: failure of restore command at 02/25/2005 17:21:52

RMAN-06495: must explicitly specify DBID with SET DBID command

RMAN > exit

So to use RMAN, you must know DBID in advance

If we knew (DBID=3171861129)

RMAN > set dbid=3171861129-- > > Note that you do not need to specify DBID if startup nomount is executed on RMAN

Executing command: SET DBID

RMAN > restore controlfile from autobackup

Launch restore in 25-February-05

Assigned channel: ORA_DISK_1

Channel ORA_DISK_1: sid=13 devtype=DISK

Channel ORA_DISK_1: look for automatic backups on the following dates: 20050225

Channel ORA_DISK_1: look for automatic backups on the following dates: 20050224

Channel ORA_DISK_1: look for automatic backups on the following dates: 20050223

Channel ORA_DISK_1: look for automatic backups on the following dates: 20050222

Channel ORA_DISK_1: look for automatic backups on the following dates: 20050221

Channel ORA_DISK_1: look for automatic backups on the following dates: 20050220

Channel ORA_DISK_1: look for automatic backups on the following dates: 20050219

Channel ORA_DISK_1: automatic backup within 7 days not found

RMAN-00571: =

RMAN-00569: = ERROR MESSAGE STACK FOLLOWS =

RMAN-00571: =

RMAN-03002: failure of restore command at 02/25/2005 17:26:46

RMAN-06172: no autobackup found or specified handle is not a valid copy or piece

Because the catalog is broken, the backup information is gone, but the automatic control file backup provides a special format, which can not read catalog or control the file information.

To restore the control file

We can see that now RMAN can not automatically find the path of automatic backup.

RMAN > SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO'/ temp/%F';-- > > can be recovered through the specified path

Executing command: SET CONTROLFILE AUTOBACKUP FORMAT

RMAN > restore controlfile from autobackup

Launch restore in 25-February-05

Use channel ORA_DISK_1

Channel ORA_DISK_1: look for automatic backups on the following dates: 20050225

Channel ORA_DISK_1: found automatic backup: / temp/c-3171861129-20050225-00

Channel ORA_DISK_1: recovery of control files from automatic backup completed

Copying control files

Output file name = C:\ ORACLE\ ORADATA\ PDF\ CONTROL01.CTL

Output file name = C:\ ORACLE\ ORADATA\ PDF\ CONTROL02.CTL

Output file name = C:\ ORACLE\ ORADATA\ PDF\ CONTROL03.CTL

Completion of restore in 25-February-05

Use cold backup to restore database

Use cold backup control files and data files

SQL > shutdown

ORA-01109:?

The database has been uninstalled.

The ORACLE routine has been closed.

SQL > startup

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

ORA-01991:?'C:\ oracle\ ora92\ DATABASE\ PWDtestdb.ORA'

SQL > select count (*) from tab

Select count (*) from tab

ERROR is on line 1:

ORA-01219:?:?

SQL > alter database open

Alter database open

ERROR is on line 1:

ORA-00314: 1 (1)

ORA-00312:? 1? 1:'C:\ ORACLE\ ORADATA\ TESTDB\ REDO01.LOG'-- > here REDO is useless, but because there is no backup of REDO, it cannot be opened.

SQL > alter database open resetlogs

Alter database open resetlogs

ERROR is on line 1:

ORA-01139: RESETLOGS?

SQL > alter database open noresetlogs

Alter database open noresetlogs

ERROR is on line 1:

ORA-01139: RESETLOGS?

SQL > alter database open

Alter database open

ERROR is on line 1:

ORA-00314: 1 (1)

ORA-00312: 1:'C:\ ORACLE\ ORADATA\ TESTDB\ REDO01.LOG'

SQL > alter database clear logfile group 1

The database has changed.

SQL > alter database clear logfile group 2

The database has changed.

SQL > alter database clear logfile group 3

Alter database clear logfile group 3

ERROR is on line 1:

ORA-00350: 3 (1)?

ORA-00312:? 3? 1:'C:\ ORACLE\ ORADATA\ TESTDB\ REDO03.LOG'

SQL > alter database open

Alter database open

ERROR is on line 1:

ORA-00338: 3 (1)?

ORA-00312:? 3? 1:'C:\ ORACLE\ ORADATA\ TESTDB\ REDO03.LOG'

SQL > alter database clear UNARCHIVED logfile group 3

Alter database clear UNARCHIVED logfile group 3

ERROR is on line 1:

ORA-00338: 3 (1)?

ORA-00312:? 3? 1:'C:\ ORACLE\ ORADATA\ TESTDB\ REDO03.LOG'

Method 1

SQL > recover database using backup controlfile

ORA-00279: / / 48540 (/ 02 / 28 / 2005 10:06:38)

ORA-00289:?: C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_3.DBF

ORA-00280:?? 48540? 1? # 3?

Specify log: {= suggested | filename | AUTO | CANCEL}

Auto

ORA-00279: / / 68658 (/ 02 / 28 / 2005 10:22:51)

ORA-00289:?: C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_4.DBF

ORA-00280:?? 68658? 1? # 4?

ORA-00278:? 'C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1room3.DBF'

ORA-00308:? 'C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1room4.DBF'

ORA-27041:?

OSD-04002: n ^ 7 (4r?*ND recover database using backup controlfile using cancel)

ORA-01906:?? BACKUP?

SQL > recover database using backup controlfile until cancel

ORA-00279: / / 68658 (/ 02 / 28 / 2005 10:22:51)

ORA-00289:?: C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_4.DBF

ORA-00280:?? 68658? 1? # 4?

Specify log: {= suggested | filename | AUTO | CANCEL}

Cancel

Media recovery has been cancelled.

SQL >

SQL > alter database open resetlogs

The database has changed.

# # #

# [method 2]-- use the creation control file trace with resetlogs

# # #

SQL > startup nomount

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

SQL > CREATE CONTROLFILE REUSE DATABASE "TESTDB" RESETLOGS ARCHIVELOG-- here we use resetlogs

2-- SET STANDBY TO MAXIMIZE PERFORMANCE

3 MAXLOGFILES 5

4 MAXLOGMEMBERS 3

5 MAXDATAFILES 100

6 MAXINSTANCES 1

7 MAXLOGHISTORY 226

8 LOGFILE

9 GROUP 1'C:\ ORACLE\ ORADATA\ TESTDB\ REDO01.LOG' SIZE 100m

10 GROUP 2'C:\ ORACLE\ ORADATA\ TESTDB\ REDO02.LOG' SIZE 100m

11 GROUP 3'C:\ ORACLE\ ORADATA\ TESTDB\ REDO03.LOG' SIZE 100m

12-STANDBY LOGFILE

13 DATAFILE

14'C:\ ORACLE\ ORADATA\ TESTDB\ SYSTEM01.DBF'

15'C:\ ORACLE\ ORADATA\ TESTDB\ UNDOTBS01.DBF'

16'C:\ ORACLE\ ORADATA\ TESTDB\ INDX01.DBF'

17'C:\ ORACLE\ ORADATA\ TESTDB\ TOOLS01.DBF'

18'C:\ ORACLE\ ORADATA\ TESTDB\ USERS01.DBF'

19 CHARACTER SET ZHS16GBK

Control file has been created

SQL > alter database open resetlogs

The database has changed.

This will lose some data.

# [method 3] use trace of noresetlog to restore

SQL > shutdown abort

The ORACLE routine has been closed.

SQL > startup nomount

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

SQL >

SQL > CREATE CONTROLFILE REUSE DATABASE "DF" NORESETLOGS ARCHIVELOG

2-- SET STANDBY TO MAXIMIZE PERFORMANCE

3 MAXLOGFILES 5

4 MAXLOGMEMBERS 3

5 MAXDATAFILES 100

6 MAXINSTANCES 1

7 MAXLOGHISTORY 226

8 LOGFILE

9 GROUP 1'C:\ ORACLE\ ORADATA\ PDF\ REDO01.LOG' SIZE 100m

10 GROUP 2'C:\ ORACLE\ ORADATA\ PDF\ REDO02.LOG' SIZE 100m

11 GROUP 3'C:\ ORACLE\ ORADATA\ PDF\ REDO03.LOG' SIZE 100m

12-STANDBY LOGFILE

13 DATAFILE

14'C:\ ORACLE\ ORADATA\ PDF\ SYSTEM01.DBF'

15'C:\ ORACLE\ ORADATA\ PDF\ UNDOTBS01.DBF'

16'C:\ ORACLE\ ORADATA\ PDF\ INDX01.DBF'

17'C:\ ORACLE\ ORADATA\ PDF\ TOOLS01.DBF'

18'C:\ ORACLE\ ORADATA\ PDF\ USERS01.DBF'

19 CHARACTER SET ZHS16GBK

Control file has been created

SQL > alter database open

Alter database open

ERROR is on line 1:

ORA-01113:?? 1?

ORA-01110: 1:'C:\ ORACLE\ ORADATA\ PDF\ SYSTEM01.DBF'

SQL > startup

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

The database is loaded.

ORA-01113: file 1 requires media recovery

ORA-01110: data file 1:'C:\ ORACLE\ ORADATA\ PDF\ SYSTEM01.DBF'

SQL > recover database;-- because shutdown abort is closed, it needs to be restored.

Complete the media recovery.

SQL > alter database open

The database has changed.

# [method 4] then we control the data files of the database and

# will it be necessary to restore all online log files when they are backed up?

SQL > startup

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

The database is loaded.

The database is already open.

SQL > shutdown

The database has been closed.

The database has been uninstalled.

The ORACLE routine has been closed.

Back up control files, data files, and online log files

SQL > startup

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

The database is loaded.

The database is already open.

SQL > select count (*) from test

COUNT (*)

12330

SQL > create table arch (status varchar (2))

The table has been created.

SQL > alter system switch logfile

The system has changed.

SQL > insert into arch select 'ok' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

SQL > shutdown

The database has been closed.

The database has been uninstalled.

The ORACLE routine has been closed.

Restore control files, data files, and online log files

SQL > startup

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

The database is loaded.

The database is already open.

SQL > select count (*) from test

COUNT (*)

12330

SQL > select count (*) from arch

Select count (*) from arch

ERROR is on line 1:

ORA-00942: table or view does not exist

SQL > archive log list

Database log mode archive mode

Automatic archiving enabled

Archive end C:\ oracle\ oradata\ testdb\ archive

The earliest summary log sequence 1

Next archive log sequence 3

Current log sequence 3

~ ~

Database cold backup if all data is included

File control files and online logs, then

The database can be opened directly, if not in the

Line log, then create controlfile is required.

... Resetlogs

Or recover database until cancel.

Alter database open resetlogs

~ ~

# # #

# but because the online log is also overwritten, the previous archive 3 and sequence 4 have

# has not been applied, so although our sequence 4 is forcibly overwritten, then 3 is actually

# is archived, can the arch table we built be recovered?

# here let's see if we can back up (control files after cold backup).

# files for recovery

# [Test 5] We simulate online log corruption and use backup to control the recovery of files and backups of data files

# # #

SQL > create table arch (status varchar (2))

The table has been created.

SQL > alter system switch logfile

The system has changed.

SQL > insert into arch select 'ok' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

Alter database backup controlfile to'C:\ oracle\ oradata\ testdb\ bak2\ controlfile.ctl'

SQL > shutdown

The database has been closed.

The database has been uninstalled.

The ORACLE routine has been closed.

Copy the original cold backup data files and online logs (in fact, online logs are useless), do not copy the original cold backup control files

SQL > startup mount

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

The database is loaded.

SQL > recover database using backup controlfile

ORA-00279: a change of 69257 (generated at 11:08:18 at 02Universe 28 ORA-00279) is required for thread 1

ORA-00289: suggestion: C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_3.DBF

ORA-00280: change 69257 is made in sequence # 3 for thread 1

Specify log: {= suggested | filename | AUTO | CANCEL}

Auto

ORA-00279: a change of 69776 (generated at 11:23:24 at 02Universe 28 ORA-00279) is required for thread 1

ORA-00289: suggestion: C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_4.DBF

ORA-00280: change 69776 is made in sequence # 4 for thread 1

ORA-00278: the log file'C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_3.DBF is no longer required for this restore

ORA-00308: unable to open archive log'C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1archive 4.DBF'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) the system cannot find the specified file.

SQL > alter database open

Alter database open

ERROR is on line 1:

ORA-01589: to open the database, you must use the RESETLOGS or NORESETLOGS option

SQL > alter database open resetlogs

Alter database open resetlogs

ERROR is on line 1:

ORA-01113: file 1 requires media recovery

ORA-01110: data file 1:'C:\ ORACLE\ ORADATA\ TESTDB\ SYSTEM01.DBF'

SQL > recover database using backup controlfile until cancel

ORA-00279: a change of 69776 (generated at 11:23:24 at 02Universe 28 ORA-00279) is required for thread 1

ORA-00289: suggestion: C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_4.DBF

ORA-00280: change 69776 is made in sequence # 4 for thread 1

Specify log: {= suggested | filename | AUTO | CANCEL}

Cancel

Media recovery has been cancelled.

SQL > alter database open resetlogs

The database has changed.

SQL > select count (*) from arch

COUNT (*)

~ ~ successfully, the result is that there is a table arch, but there is no data, that is, no online log is applied. In fact, if you don't restore the online log here (instead of disturbing it)

We can do a full recovery with recover database, because the control file is actually current, if you use the control file backed up later.

Because the control file can also be restored after the arch table has been built.

The above method is also after the online log is destroyed, there is the original cold backup (or hot backup), using the current control file (in fact, it is no longer current

We can see later that using using backup controlfile) can restore to the last archive log, but lose the data of the online log.

~ ~ ~

# #

Backup of RMAN is available, and catalog is normal

# #

-- need to build a catalog library

# #

Control file corruption, with trace, inactive in

The line log is damaged

# #

SQL > archive log list

Database log mode archive mode

Automatic archiving enabled

Archive end C:\ oracle\ oradata\ testdb\ archive

The earliest summary log sequence 0

Next archive log sequence 1

Current log sequence 1

SQL > select count (*) from test

COUNT (*)

12330

SQL > create table arch (status varchar (2))

The table has been created.

SQL > alter system switch logfile

The system has changed.

SQL > insert into arch select 'ok' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

SQL > archive log list

Database log mode archive mode

Automatic archiving enabled

Archive end C:\ oracle\ oradata\ testdb\ archive

The earliest summary log sequence 1

Next archive log sequence 2

Current log sequence 2

SQL >

SQL > select GROUP#,SEQUENCE#,archived, STATUS from v$log

GROUP# SEQUENCE# ARC STATUS

1 2 NO CURRENT

2 0 YES UNUSED

3 1 YES INACTIVE-off-line log

GROUP# MEMBER

3 C:\ ORACLE\ ORADATA\ TESTDB\ REDO03.LOG

Destroy Log 3 with ultraedit

SQL > insert into arch select 'ok' from dba_objects

6166 rows were created. -- > > if the offline log is corrupted, the database can still execute

SQL > alter system switch logfile

The system has changed.

SQL > select GROUP#,SEQUENCE#,archived, STATUS from v$log

GROUP# SEQUENCE# ARC STATUS

1 2 YES ACTIVE

2 3 NO CURRENT

3 1 YES INACTIVE

SQL > alter system switch logfile;-- > > error is reported when switching to log group 3

Alter system switch logfile

ERROR is on line 1:

ORA-03113: end of file for communication channel

# #

# [method 1] use clear group

# #

SQL > startup mount

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

The database is loaded.

SQL > select GROUP#,SEQUENCE#,archived, STATUS from v$log

GROUP# SEQUENCE# ARC STATUS

1 2 YES ACTIVE

2 3 NO INVALIDATED

3 0 YES UNUSED

SQL > alter database clear logfile group 3

Alter database clear logfile group 3

ERROR is on line 1:

ORA-00344: unable to recreate the online log'C:\ ORACLE\ ORADATA\ TESTDB\ REDO03.LOG'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 32) the process cannot access the file because it is being used by another program.

SQL > select GROUP#,SEQUENCE#,archived, STATUS from v$log

GROUP# SEQUENCE# ARC STATUS

1 2 YES ACTIVE

2 3 NO INVALIDATED

3 0 YES CLEARING-the above statement has already performed the clear function

SQL > alter database open

The database has changed.

SQL > select GROUP#,SEQUENCE#,archived, STATUS from v$log

GROUP# SEQUENCE# ARC STATUS

1 4 NO CURRENT

2 3 YES INACTIVE

3 0 YES CLEARING-skip damaged log groups directly

SQL > select count (*) from arch

COUNT (*)

6166-- > > data is not lost

# #

# [method 2] use trace resetlogs

# #

SQL > create table arch (status varchar (2))

The table has been created.

SQL > alter system switch logfile

The system has changed.

SQL > insert into arch select 'ok' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

SQL > archive log list

Database log mode archive mode

Automatic archiving enabled

Archive end C:\ oracle\ oradata\ testdb\ archive

The earliest summary log sequence 1

Next archive log sequence 2

Current log sequence 2

SQL > insert into arch select 'ok' from dba_objects

6166 rows were created.

SQL > alter system switch logfile

The system has changed.

SQL > insert into arch select 'no' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

SQL > select GROUP#,SEQUENCE#,archived, STATUS from v$log

GROUP# SEQUENCE# ARC STATUS

1 2 YES ACTIVE

2 1 YES INACTIVE

3 3 NO CURRENT

SQL > archive log list

Database log mode archive mode

Automatic archiving enabled

Archive end C:\ oracle\ oradata\ testdb\ archive

The earliest summary log sequence 1

Next archive log sequence 3

Current log sequence 3

SQL >

SQL > shutdown

~ ~

At this time, because it is a normal shutdown,arch table, the state is equal to

The data of no has been written into the data file

~ ~

Simulation Log 2 error

SQL > startup nomount

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

SQL > CREATE CONTROLFILE REUSE DATABASE "testdb" RESETLOGS AR

2-- SET STANDBY TO MAXIMIZE PERFORMANCE

3 MAXLOGFILES 5

4 MAXLOGMEMBERS 3

5 MAXDATAFILES 100

6 MAXINSTANCES 1

7 MAXLOGHISTORY 226

8 LOGFILE

9 GROUP 1'C:\ ORACLE\ ORADATA\ testdb\ REDO01.LOG' SIZE 10

10 GROUP 2'C:\ ORACLE\ ORADATA\ testdb\ REDO02.LOG' SIZE 10

11 GROUP 3'C:\ ORACLE\ ORADATA\ testdb\ REDO03.LOG' SIZE 10

12-STANDBY LOGFILE

13 DATAFILE

14'C:\ ORACLE\ ORADATA\ testdb\ SYSTEM01.DBF'

15'C:\ ORACLE\ ORADATA\ testdb\ UNDOTBS01.DBF'

16'C:\ ORACLE\ ORADATA\ testdb\ INDX01.DBF'

17'C:\ ORACLE\ ORADATA\ testdb\ TOOLS01.DBF'

18'C:\ ORACLE\ ORADATA\ testdb\ USERS01.DBF'

19 CHARACTER SET ZHS16GBK

Control file has been created

SQL > alter database open resetlogs

The database has changed.

SQL > select count (*), status from arch group by status

COUNT (*) ST

6166 no-- > > We found that no data was lost in the online log here.

12332 ok

~ ~ ~

Creating control files with resetlogs and noresetlogs here will not lose data.

Because the normal shutdown data has been written into the data file.

~ ~ ~

So if we assume that we are using the old data backup, will we use this data when rolling forward?

After we made an error in Simulation Log 2, we copied the data file backup.

~ ~ ~

# # #

# [Test 3] data file corruption, control file corruption, trace

# using resetlogs, non-online logs are corrupted

# # #

SQL > create table arch (status varchar (2));-Archive 1

The table has been created.

SQL > alter system switch logfile

The system has changed.

SQL > insert into arch select 'ok' from dba_objects;-in Archive 2

6166 rows were created.

SQL > commit

The submission is complete.

SQL > insert into arch select 'ok' from dba_objects

6166 rows were created.

SQL > alter system switch logfile

The system has changed.

SQL > insert into arch select 'no' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

SQL > select GROUP#,SEQUENCE#,archived, STATUS from v$log

GROUP# SEQUENCE# ARC STATUS

1 2 YES ACTIVE

2 1 YES INACTIVE

3 3 NO CURRENT

SQL >

SQL > shutdown

The database has been closed.

The database has been uninstalled.

The ORACLE routine has been closed.

Here simulate log filegroup 2 error, control file corruption, data file corruption

SQL >

SQL > startup mount

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

ORA-01991:?'C:\ oracle\ ora92\ DATABASE\ PWDtestdb.ORA'

SQL > shutdown

ORA-01109:?

The database has been uninstalled.

The ORACLE routine has been closed.

Restore data files

SQL > startup nomount

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

SQL > CREATE CONTROLFILE REUSE DATABASE "testdb" RESETLOGS ARCHIVELOG

2-- SET STANDBY TO MAXIMIZE PERFORMANCE

3 MAXLOGFILES 5

4 MAXLOGMEMBERS 3

5 MAXDATAFILES 100

6 MAXINSTANCES 1

7 MAXLOGHISTORY 226

8 LOGFILE

9 GROUP 1'C:\ ORACLE\ ORADATA\ testdb\ REDO01.LOG' SIZE 100m

10 GROUP 2'C:\ ORACLE\ ORADATA\ testdb\ REDO02.LOG' SIZE 100m

11 GROUP 3'C:\ ORACLE\ ORADATA\ testdb\ REDO03.LOG' SIZE 100m

12-STANDBY LOGFILE

13 DATAFILE

14'C:\ ORACLE\ ORADATA\ testdb\ SYSTEM01.DBF'

15'C:\ ORACLE\ ORADATA\ testdb\ UNDOTBS01.DBF'

16'C:\ ORACLE\ ORADATA\ testdb\ INDX01.DBF'

17'C:\ ORACLE\ ORADATA\ testdb\ TOOLS01.DBF'

18'C:\ ORACLE\ ORADATA\ testdb\ USERS01.DBF'

19 CHARACTER SET ZHS16GBK

Control file has been created

SQL > alter database mount

Alter database mount

ERROR is on line 1:

ORA-01100:?

SQL > shutdown

ORA-01109:?

The database has been uninstalled.

The ORACLE routine has been closed.

SQL > startup mount

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

The database is loaded.

SQL > recover database using backup controlfile using cancel

ORA-01906: requires the BACKUP keyword

SQL > recover database using backup controlfile until cancel

ORA-00279: a change of 69257 (generated at 11:08:18 at 02Universe 28 ORA-00279) is required for thread 1

ORA-00289: suggestion: C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_3.DBF

ORA-00280: change 69257 is made in sequence # 3 for thread 1

Specify log: {= suggested | filename | AUTO | CANCEL}

Cancel

Media recovery has been cancelled.

SQL > alter database open resetlogs

The database has changed.

SQL > select count (*), status from arch group by status

Select count (*), status from arch group by status

ERROR is on line 1:

ORA-00942: table or view does not exist

SQL > select count (*) from arch

Select count (*) from arch

ERROR is on line 1:

ORA-00942: table or view does not exist

SQL > select count (*) from test

COUNT (*)

12330

~ ~ ~

It will not be fully recovered with resetlogs, because the data files have also been restored

So read the online log and roll forward, but now it's resetlogs.

What if we use noresetlogs to create control files?

~ ~ ~

# # #

# [Test 4] data file corruption, control file corruption, trace

# using noresetlogs, non-online log corruption, shutdown abort

# # #

SQL > shutdown

ORA-01109: the database is not open

The database has been uninstalled.

The ORACLE routine has been closed.

SQL > startup

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

ORA-01991:?'C:\ oracle\ ora92\ DATABASE\ PWDtestdb.ORA'

SQL > alter database open

The database has changed.

SQL > create table arch (status varchar (2)) tablespace users

The table has been created.

SQL > alter system switch logfile

The system has changed.

SQL > insert into arch select 'ok' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

SQL > insert into arch select * from arch

6166 rows were created.

SQL > /

12332 rows were created.

SQL > /

24664 rows were created.

SQL > /

49328 rows were created.

SQL > commit

The submission is complete.

SQL > alter system switch logfile

The system has changed.

SQL > insert into arch select 'no' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

SQL > select GROUP#,SEQUENCE#,archived, STATUS from v$log

GROUP# SEQUENCE# ARC STATUS

1 5 NO CURRENT

2 3 YES INACTIVE

3 4 YES ACTIVE

SQL > select resetlogs_change#-1 control FILECTROLFILER CHANGE # from v$database

RESETLOGS_CHANGE#-1 CONTROLFILE_CHANGE#

68658 69641

SQL > shutdown abort

The ORACLE routine has been closed.

SQL > startup

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

ORA-01991:?'C:\ oracle\ ora92\ DATABASE\ PWDtestdb.ORA'

SQL > alter database open

The database has changed.

SQL > insert into arch select 'af' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

SQL > select GROUP#,SEQUENCE#,archived, STATUS from v$log

GROUP# SEQUENCE# ARC STATUS

1 5 YES INACTIVE

2 6 NO CURRENT

3 4 YES INACTIVE

SQL > shutdown abort

The ORACLE routine has been closed.

SQL > exit

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 disconnected

C:\ Documents and Settings\ lifeng.fang > sqlplus "sys/sunsdl as sysdba"

SQL*Plus: Release 9.2.0.1.0-Production on Wednesday March 2 16:29:44 2005

Connected to an idle routine.

SQL > startup nomount

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

SQL > CREATE CONTROLFILE REUSE DATABASE "testdb" NORESETLOGS ARCHIVELOG

2-- SET STANDBY TO MAXIMIZE PERFORMANCE

3 MAXLOGFILES 5

4 MAXLOGMEMBERS 3

5 MAXDATAFILES 100

6 MAXINSTANCES 1

7 MAXLOGHISTORY 226

8 LOGFILE

9 GROUP 1'C:\ ORACLE\ ORADATA\ testdb\ REDO01.LOG' SIZE 100m

10 GROUP 2'C:\ ORACLE\ ORADATA\ testdb\ REDO02.LOG' SIZE 100m

11 GROUP 3'C:\ ORACLE\ ORADATA\ testdb\ REDO03.LOG' SIZE 100m-this offline log is missing

12-STANDBY LOGFILE

13 DATAFILE

14'C:\ ORACLE\ ORADATA\ testdb\ SYSTEM01.DBF'

15'C:\ ORACLE\ ORADATA\ testdb\ UNDOTBS01.DBF'

16'C:\ ORACLE\ ORADATA\ testdb\ INDX01.DBF'

17'C:\ ORACLE\ ORADATA\ testdb\ TOOLS01.DBF'

18'C:\ ORACLE\ ORADATA\ testdb\ USERS01.DBF'

19 CHARACTER SET ZHS16GBK

CREATE CONTROLFILE REUSE DATABASE "testdb" NORESETLOGS ARCHIVELOG

ERROR is on line 1:

ORA-01503: CREATE CONTROLFILE??

ORA-01565: C:\ ORACLE\ ORADATA\ testdb\ REDO03.LOG'???

ORA-27041:?

OSD-04002: n ^ 7 (4r?*ND alter database open)

Alter database open

ERROR is on line 1:

ORA-01113:?? 1?

ORA-01110: 1:'C:\ ORACLE\ ORADATA\ TESTDB\ SYSTEM01.DBF'

SQL > recover database

ORA-00279:? 69456? (? 03? 02? 10:26:30?

ORA-00289:?: C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_3.DBF

ORA-00280:?? 69456? 1? # 3?

Specify log: {= suggested | filename | AUTO | CANCEL}

Auto

ORA-00279:? 69594? (? 03? 02? 16:27:24?

ORA-00289:?: C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_4.DBF

ORA-00280:?? 69594? 1? # 4?

ORA-00278:? 'C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1room3.DBF'

Applied logs.

Complete the media recovery.

SQL > alter database open

The database has changed.

SQL > select count (*), status from arch group by status

COUNT (*) ST

6166 af

6166 no

98656 ok

This situation can be fully recovered with noresetlogs.

[Last edited by fly115 on 2005-3-21 at 19:56]

UID393 post 3566 essence 23 points 3803 circulation points 3803 points money 0 yuan reading rights 225online time 173hours registration time 2003-1-21 finally log in 2008-8-27 to view details

TOP

Fly115

Super moderator

Send a short message in personal space and add it as a friend. Currently offline, primary and secondary schools on the 4th floor were published on 2005-3-19 18:00 only to see the author.

# #

# [1.5] there is trace, and the activity online log is corrupted

# #

# [1.5.1] there are trace, activity online log corruption, normal shutdown or current data file

# #

SQL > create table arch (status varchar (2))

The table has been created.

SQL > alter system switch logfile

The system has changed.

SQL > insert into arch select 'ok' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

SQL > insert into arch select 'ok' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

SQL > alter system switch logfile

The system has changed.

SQL > insert into arch select 'no' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

SQL > select GROUP#,SEQUENCE#,archived, STATUS from v$log

GROUP# SEQUENCE# ARC STATUS

1 2 YES ACTIVE

2 1 YES ACTIVE

3 3 NO CURRENT

SQL > shutdown-- shut down the database normally

The database has been closed.

The database has been uninstalled.

The ORACLE routine has been closed.

The simulation deletes the current online log group 3 at this time

SQL >

SQL > startup nomount

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

SQL > CREATE CONTROLFILE REUSE DATABASE "testdb" RESETLOGS ARCHIVELOG

2-- SET STANDBY TO MAXIMIZE PERFORMANCE

3 MAXLOGFILES 5

4 MAXLOGMEMBERS 3

5 MAXDATAFILES 100

6 MAXINSTANCES 1

7 MAXLOGHISTORY 226

8 LOGFILE

9 GROUP 1'C:\ ORACLE\ ORADATA\ testdb\ REDO01.LOG' SIZE 100m

10 GROUP 2'C:\ ORACLE\ ORADATA\ testdb\ REDO02.LOG' SIZE 100m

11 GROUP 3'C:\ ORACLE\ ORADATA\ testdb\ REDO03.LOG' SIZE 100m

12-STANDBY LOGFILE

13 DATAFILE

14'C:\ ORACLE\ ORADATA\ testdb\ SYSTEM01.DBF'

15'C:\ ORACLE\ ORADATA\ testdb\ UNDOTBS01.DBF'

16'C:\ ORACLE\ ORADATA\ testdb\ INDX01.DBF'

17'C:\ ORACLE\ ORADATA\ testdb\ TOOLS01.DBF'

18'C:\ ORACLE\ ORADATA\ testdb\ USERS01.DBF'

19 CHARACTER SET ZHS16GBK

Control file has been created

SQL > alter database open resetlogs

The database has changed.

SQL > select count (*), status from arch group by status

COUNT (*) ST

6166 no

12332 ok

-- > > No data has been lost, because your data has been written into the data file.

# #

# [1.5.2] there are trace, activity online log corruption, shutdown abort or non-current data file

The current online log file is corrupted

# #

# # #

[test 1] shutdown abort, backup control file or trace of resetlog

There is no backup of data files, so the method of forcing to open the database is adopted.

# # #

SQL > create table arch (status varchar (2))

The table has been created.

SQL > alter system switch logfile

The system has changed.

SQL > insert into arch select 'ok' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

SQL > insert into arch select 'ok' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

SQL > alter system switch logfile

SQL > insert into arch select 'no' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

SQL > select GROUP#,SEQUENCE#,archived, STATUS from v$log

GROUP# SEQUENCE# ARC STATUS

1 2 YES ACTIVE

2 3 NO CURRENT

3 1 YES INACTIVE

SQL > insert into arch select 'no' from dba_objects

6166 rows were created.

SQL > shutdown abort

The ORACLE routine has been closed.

SQL > startup

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

The database is loaded.

ORA-00313: unable to open a member of log group 1 (thread 1)

ORA-00312: online log 1 thread 1:'C:\ ORACLE\ ORADATA\ TESTDB\ REDO01.LOG'

ORA-27047: unable to read the title block of the file

OSD-04006: ReadFile () failed to read the file

O/S-Error: (OS 38) reaches the end of the file.

SQL > alter database clear logfile group 1

Alter database clear logfile group 1

ERROR is on line 1:

ORA-01624: log 1 is required for emergency recovery of thread 1

ORA-00312: online log 1 thread 1:'C:\ ORACLE\ ORADATA\ TESTDB\ REDO01.LOG'

SQL > startup nomount

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

SQL > CREATE CONTROLFILE REUSE DATABASE "testdb" RESETLOGS ARCHIVELOG

2-- SET STANDBY TO MAXIMIZE PERFORMANCE

3 MAXLOGFILES 5

4 MAXLOGMEMBERS 3

5 MAXDATAFILES 100

6 MAXINSTANCES 1

7 MAXLOGHISTORY 226

8 LOGFILE

9 GROUP 1'C:\ ORACLE\ ORADATA\ testdb\ REDO01.LOG' SIZE 100m

10 GROUP 2'C:\ ORACLE\ ORADATA\ testdb\ REDO02.LOG' SIZE 100m

11 GROUP 3'C:\ ORACLE\ ORADATA\ testdb\ REDO03.LOG' SIZE 100m

12-STANDBY LOGFILE

13 DATAFILE

14'C:\ ORACLE\ ORADATA\ testdb\ SYSTEM01.DBF'

15'C:\ ORACLE\ ORADATA\ testdb\ UNDOTBS01.DBF'

16'C:\ ORACLE\ ORADATA\ testdb\ INDX01.DBF'

17'C:\ ORACLE\ ORADATA\ testdb\ TOOLS01.DBF'

18'C:\ ORACLE\ ORADATA\ testdb\ USERS01.DBF'

19 CHARACTER SET ZHS16GBK

Control file has been created

SQL > alter database open resetlogs

Alter database open resetlogs

ERROR is on line 1:

ORA-01194:?? 1?

ORA-01110: 1:'C:\ ORACLE\ ORADATA\ TESTDB\ SYSTEM01.DBF'

SQL > recover database using backup controlfile until cancel

ORA-00279: / / 70123 (/ 02 / 28 / 2005 15:22:05)

ORA-00289:?: C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_3.DBF

ORA-00280:?? 70123? 1? # 3?

Specify log: {= suggested | filename | AUTO | CANCEL}

Cancel

ORA-01547:??: RECOVER? OPEN RESETLOGS?

ORA-01194:?? 1?

ORA-01110: 1:'C:\ ORACLE\ ORADATA\ TESTDB\ SYSTEM01.DBF'

ORA-01112:?

We see that if it is an abort down machine, you need to apply the online activity log to open the database.

At this time, we must add implicit parameters to force the database to open, export, rebuild the database, and import business data.

SQL > show parameter spfile

NAME TYPE VALUE

Spfile string% ORACLE_HOME%\ DATABASE\ SPFILE

ORACLE_SID%.ORA

SQL > create pfile='pfiletest.ora' from spfile

The file has been created.

SQL > create pfile='c:\ pfiletest.ora' from spfile

The file has been created.

Edit c:\ pfiletest.ora

Add three parameters

_ allow_resetlogs_corruption=true

_ corrupted_rollback_segments=true

_ offline_rollback_segments=true

SQL > shutdown immediate

ORA-01109: the database is not open

The database has been uninstalled.

The ORACLE routine has been closed.

SQL >

SQL > startup pfile='c:\ pfiletest.ora'

The ORACLE routine has been started.

Total System Global Area 135338868 bytes

Fixed Size 453492 bytes

Variable Size 109051904 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

The database is loaded.

ORA-01589: to open the database, you must use the RESETLOGS or NORESETLOGS option

SQL > alter database open resetlogs

Alter database open resetlogs

ERROR is on line 1:

ORA-01092: the ORACLE routine terminates. Forcibly disconnect

SQL >

SQL > exit

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 disconnected

C:\ > sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.1.0-Production on Friday 31 December 14:03:09 2004

Connected to an idle routine.

SQL > startup pfile='c:\ pfiletest.ora'

The ORACLE routine has been started.

Total System Global Area 135338868 bytes

Fixed Size 453492 bytes

Variable Size 109051904 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

The database is loaded.

The database is already open.

SQL >

# # #

# [Test 2] trace of shutdown abort,resetlog

# data file backup, current online log corruption, control file corruption, trace

# # #

SQL > create table arch (status varchar (2)) tablespace users

The table has been created.

SQL > alter system switch logfile

The system has changed.

SQL > insert into arch select 'ok' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

SQL > insert into arch select * from arch

6166 rows were created.

SQL > /

12332 rows were created.

SQL > /

24664 rows were created.

SQL > /

49328 rows were created.

SQL > commit

The submission is complete.

SQL > alter system switch logfile

The system has changed.

SQL > insert into arch select 'no' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

SQL > select GROUP#,SEQUENCE#,archived, STATUS from v$log

GROUP# SEQUENCE# ARC STATUS

1 5 NO CURRENT

2 3 YES ACTIVE

3 4 NO ACTIVE

SQL > shutdown abort

The ORACLE routine has been closed.

SQL > startup nomount

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

SQL > CREATE CONTROLFILE REUSE DATABASE "testdb" NORESETLOGS ARCHIVELOG

2-- SET STANDBY TO MAXIMIZE PERFORMANCE

3 MAXLOGFILES 5

4 MAXLOGMEMBERS 3

5 MAXDATAFILES 100

6 MAXINSTANCES 1

7 MAXLOGHISTORY 226

8 LOGFILE

9 GROUP 2'C:\ ORACLE\ ORADATA\ testdb\ REDO02.LOG' SIZE 100m

10 GROUP 3'C:\ ORACLE\ ORADATA\ testdb\ REDO03.LOG' SIZE 100m

11-- STANDBY LOGFILE

12 DATAFILE

13'C:\ ORACLE\ ORADATA\ testdb\ SYSTEM01.DBF'

14'C:\ ORACLE\ ORADATA\ testdb\ UNDOTBS01.DBF'

15'C:\ ORACLE\ ORADATA\ testdb\ INDX01.DBF'

16'C:\ ORACLE\ ORADATA\ testdb\ TOOLS01.DBF'

17'C:\ ORACLE\ ORADATA\ testdb\ USERS01.DBF'

18 CHARACTER SET ZHS16GBK

CREATE CONTROLFILE REUSE DATABASE "testdb" NORESETLOGS ARCHIVELOG

ERROR is on line 1:

ORA-01503: CREATE CONTROLFILE??

ORA-01192:?

~ ~

If it's NORESETLOGS, then he needs to check the current online log, which is no longer available.

SQL > CREATE CONTROLFILE REUSE DATABASE "testdb" NORESETLOGS ARCHIVELOG

2-- SET STANDBY TO MAXIMIZE PERFORMANCE

3 MAXLOGFILES 5

4 MAXLOGMEMBERS 3

5 MAXDATAFILES 100

6 MAXINSTANCES 1

7 MAXLOGHISTORY 226

8 LOGFILE

9 GROUP 1'C:\ ORACLE\ ORADATA\ testdb\ REDO01.LOG' SIZE 100m

10 GROUP 2'C:\ ORACLE\ ORADATA\ testdb\ REDO02.LOG' SIZE 100m

11 GROUP 3'C:\ ORACLE\ ORADATA\ testdb\ REDO03.LOG' SIZE 100m

12-STANDBY LOGFILE

13 DATAFILE

14'C:\ ORACLE\ ORADATA\ testdb\ SYSTEM01.DBF'

15'C:\ ORACLE\ ORADATA\ testdb\ UNDOTBS01.DBF'

16'C:\ ORACLE\ ORADATA\ testdb\ INDX01.DBF'

17'C:\ ORACLE\ ORADATA\ testdb\ TOOLS01.DBF'

18'C:\ ORACLE\ ORADATA\ testdb\ USERS01.DBF'

19 CHARACTER SET ZHS16GBK

CREATE CONTROLFILE REUSE DATABASE "testdb" NORESETLOGS ARCHIVELOG

ERROR is on line 1:

ORA-01503: CREATE CONTROLFILE??

ORA-01565: C:\ ORACLE\ ORADATA\ testdb\ REDO01.LOG'???-- > this file is gone

ORA-27041:?

OSD-04002: n ^ 7 (4rpm) ND7CNJ #

SQL > shutdown

ORA-01507:?

The ORACLE routine has been closed.

SQL > startup nomount

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

SQL > CREATE CONTROLFILE REUSE DATABASE "testdb" RESETLOGS ARCHIVELOG

2-- SET STANDBY TO MAXIMIZE PERFORMANCE

3 MAXLOGFILES 5

4 MAXLOGMEMBERS 3

5 MAXDATAFILES 100

6 MAXINSTANCES 1

7 MAXLOGHISTORY 226

8 LOGFILE

9 GROUP 2'C:\ ORACLE\ ORADATA\ testdb\ REDO02.LOG' SIZE 100m

10 GROUP 3'C:\ ORACLE\ ORADATA\ testdb\ REDO03.LOG' SIZE 100m

11-- STANDBY LOGFILE

12 DATAFILE

13'C:\ ORACLE\ ORADATA\ testdb\ SYSTEM01.DBF'

14'C:\ ORACLE\ ORADATA\ testdb\ UNDOTBS01.DBF'

15'C:\ ORACLE\ ORADATA\ testdb\ INDX01.DBF'

16'C:\ ORACLE\ ORADATA\ testdb\ TOOLS01.DBF'

17'C:\ ORACLE\ ORADATA\ testdb\ USERS01.DBF'

18 CHARACTER SET ZHS16GBK

Control file has been created

SQL > alter database open

Alter database open

ERROR is on line 1:

ORA-01589:? RESETLOGS? NORESETLOGS??

SQL > alter database open resetlogs

Alter database open resetlogs

ERROR is on line 1:

ORA-01152:?? 1?

ORA-01110: 1:'C:\ ORACLE\ ORADATA\ TESTDB\ SYSTEM01.DBF'

SQL > recover database using backup controlfile until cancel

ORA-00279:? 69456? (? 03? 02? 10:26:30?

ORA-00289:?: C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_3.DBF

ORA-00280:?? 69456? 1? # 3?

Specify log: {= suggested | filename | AUTO | CANCEL}

ORA-00279:? 69616? (? 03? 02? 16:53:52?

ORA-00289:?: C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_4.DBF

ORA-00280:?? 69616? 1? # 4?

ORA-00278:? 'C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1room3.DBF'

Specify log: {= suggested | filename | AUTO | CANCEL}

ORA-00279:? 69661? (? 03? 02? 16:53:54?

ORA-00289:?: C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_5.DBF

ORA-00280:?? 69661? 1? # 5?

ORA-00278:? 'C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1room4.DBF'

Specify log: {= suggested | filename | AUTO | CANCEL}

ORA-00308:? 'C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1' 5.DBF'

ORA-27041:?

OSD-04002: n ^ 7 (4r?*ND alter database open resetlogs)

The database has changed.

SQL > select count (*), status from arch group by status

COUNT (*) ST

98656 ok-some data has been restored, but the data in the online log cannot be restored

If the database is backed up by abort or old data files, then the control files and data files are inconsistent and need to be restored, and the currently corrupted online logs are involved.

You still need to use the online activity log to recover when you start. If it is a normal shutdown and the old data backup is not used, then the control file and the data file are consistent.

No current online log is required. In this case, the backup of abort or old data files can only be done with backup datafile and backup controlfile.

Full recovery will result in data loss. So multiple controlfile and online redo log file are important. Usually when the database is running normally

If the current online log is destroyed, the database will be abnormal and the down machine will appear immediately, and there will be a scenario of 1.5.2.

We know that if abort is closed, then some transactions are incomplete or some transactions are not written to the data file, which is marked in the data file, so that you can use the

Create controlfile... Noresetlogs or resetlogs reads the header information of the data file when it is created, which controls the file information, such as setting the end SCN to infinity)

You need to restore the current online log when you open it.

# # #

# [Test 3] shutdown abort, control file corruption, backup control file

# the current online log is corrupted and data files are backed up

# # #

Restore the original backup data files, control files

SQL > shutdown

ORA-01109: the database is not open

The ORACLE routine has been closed.

SQL > startup mount

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

ORA-01991:?'C:\ oracle\ ora92\ DATABASE\ PWDtestdb.ORA'

SQL > recover database using backup controlfile

ORA-00279:? 69456? (? 03? 02? 10:26:30?

ORA-00289:?: C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_3.DBF

ORA-00280:?? 69456? 1? # 3?

Specify log: {= suggested | filename | AUTO | CANCEL}

ORA-00279:? 69572? (? 03? 02? 17:07:43?

ORA-00289:?: C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_4.DBF

ORA-00280:?? 69572? 1? # 4?

ORA-00278:? 'C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1room3.DBF'

Specify log: {= suggested | filename | AUTO | CANCEL}

ORA-00279:? 69617? (? 03? 02? 17:07:44?

ORA-00289:?: C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_5.DBF

ORA-00280:?? 69617? 1? # 5?

ORA-00278:? 'C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1room4.DBF'

Specify log: {= suggested | filename | AUTO | CANCEL}

ORA-00308:? 'C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1' 5.DBF'

ORA-27041:?

OSD-04002: n ^ 7 (4r?*ND select resetlogs_change#-1 no. Control FILEX CHANGE # from v$database

RESETLOGS_CHANGE#-1 CONTROLFILE_CHANGE#

68658 69617

SQL > recover database using backup controlfile until cancel

ORA-00279:? 69617? (? 03? 02? 17:07:44?

ORA-00289:?: C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_5.DBF

ORA-00280:?? 69617? 1? # 5?

Specify log: {= suggested | filename | AUTO | CANCEL}

Cancel

Media recovery has been cancelled.

SQL > select resetlogs_change#-1 control FILECTROLFILER CHANGE # from v$database

RESETLOGS_CHANGE#-1 CONTROLFILE_CHANGE#

68658 69617

SQL > alter database open resetlogs

The database has changed.

SQL > select count (*), status from arch group by status

COUNT (*) ST

98656 ok

# # #

# [1.5.3] Control file corruption if trace is not the latest database structure, such as missing a data file

# # #

SQL > create table arch (status varchar (2))

The table has been created.

SQL > alter system switch logfile

The system has changed.

SQL > insert into arch select 'ok' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

SQL > insert into arch select 'ok' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

SQL > alter system switch logfile

The system has changed.

SQL > insert into arch select 'no' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

SQL > select GROUP#,SEQUENCE#,archived, STATUS from v$log

GROUP# SEQUENCE# ARC STATUS

1 2 NO ACTIVE

2 1 YES ACTIVE

3 3 NO CURRENT

SQL > alter tablespace tools add datafile'C:\ ORACLE\ ORADATA\ TESTDB\ TOOLS02.dbf'

Size 10m

The tablespace has changed.

SQL > shutdown

The database has been closed.

The database has been uninstalled.

The ORACLE routine has been closed.

Simulation control file is missing

SQL > CREATE CONTROLFILE REUSE DATABASE "testdb" NORESETLOGS ARCHIVELOG

2-- SET STANDBY TO MAXIMIZE PERFORMANCE

3 MAXLOGFILES 5

4 MAXLOGMEMBERS 3

5 MAXDATAFILES 100

6 MAXINSTANCES 1

7 MAXLOGHISTORY 226

8 LOGFILE

9 GROUP 1'C:\ ORACLE\ ORADATA\ testdb\ REDO01.LOG' SIZE 100m

10 GROUP 2'C:\ ORACLE\ ORADATA\ testdb\ REDO02.LOG' SIZE 100m

11 GROUP 3'C:\ ORACLE\ ORADATA\ testdb\ REDO03.LOG' SIZE 100m

12-STANDBY LOGFILE

13 DATAFILE

14'C:\ ORACLE\ ORADATA\ testdb\ SYSTEM01.DBF'

15'C:\ ORACLE\ ORADATA\ testdb\ UNDOTBS01.DBF'

16'C:\ ORACLE\ ORADATA\ testdb\ INDX01.DBF'

17'C:\ ORACLE\ ORADATA\ testdb\ TOOLS01.DBF'

18'C:\ ORACLE\ ORADATA\ testdb\ USERS01.DBF'

19 CHARACTER SET ZHS16GBK

Control file has been created

SQL > alter database open

The database has changed.

SQL > select name,status from v$datafile

NAME STATUS

C:\ ORACLE\ ORADATA\ TESTDB\ SYSTEM01.DBF SYSTEM

C:\ ORACLE\ ORADATA\ TESTDB\ UNDOTBS01.DBF ONLINE

C:\ ORACLE\ ORADATA\ TESTDB\ INDX01.DBF ONLINE

C:\ ORACLE\ ORADATA\ TESTDB\ TOOLS01.DBF ONLINE

C:\ ORACLE\ ORADATA\ TESTDB\ USERS01.DBF ONLINE

C:\ ORACLE\ ORA92\ DATABASE\ MISSING00006 RECOVER-- this file is now in recover state

6 rows have been selected.

We found that C:\ ORACLE\ ORA92\ DATABASE\ MISSING00006 doesn't exist, it's just a tag.

SQL > alter database rename file 'MISSING00006' to' C:\ ORACLE\ ORADATA\ TESTDB\ TOOLS02.dbf';-- MISSING00006 do not add a path

The database has changed.

SQL > recover datafile'C:\ ORACLE\ ORADATA\ TESTDB\ TOOLS02.dbf'

Complete the media recovery.

SQL > alter database datafile'C:\ ORACLE\ ORADATA\ TESTDB\ TOOLS02.dbf' online

The database has changed.

If you use backup to control files, and backup

The control file is missing two new files.

There will be an error during recovery, and we can

Solve the problem with rename

Use backup to control files

Recover AUTOMATIC database using backup controlfile until cancel

...

ORA-00283: recovery session canceled due to errors

ORA-01244: unnamed datafile (s) added to controlfile by media recovery

ORA-01110: data file 3:'/ oracle/dbs/db2.f'

ORA-01110: data file 2:'/ oracle/dbs/db3.f'

This is because when rolling forward, we found that in the file#

There are two files, but the control file does not.

We can see it in v$datafile.

So resume the interrupt.

SELECT FILE#,NAME

FROM V$DATAFILE

FILE# NAME

1 / oracle/dbs/db1.f

2 / oracle/dbs/UNNAMED00002

3 / oracle/dbs/UNNAMED00003

At this time, the file can be renamed.

ALTER DATABASE RENAME FILE'/ db/UNNAMED00002' TO'/ oracle/dbs/db3.f'

ALTER DATABASE RENAME FILE'/ db/UNNAMED00003' TO'/ oracle/dbs/db2.f'

RECOVER AUTOMATIC DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL

# # #

# [1.5.4] Control file corruption if trace is not the latest database structure, such as missing a read-only data file

# # #

SQL > alter tablespace tools add datafile'C:\ ORACLE\ ORADATA\ TESTDB\ TOOLS02.dbf'

Size 10m

The tablespace has changed.

SQL > alter tablespace tools read only

The tablespace has changed.

SQL > select name,ENABLED

2 from v$datafile

NAME ENABLED

C:\ ORACLE\ ORADATA\ TESTDB\ SYSTEM01.DBF READ WRITE

C:\ ORACLE\ ORADATA\ TESTDB\ UNDOTBS01.DBF READ WRITE

C:\ ORACLE\ ORADATA\ TESTDB\ INDX01.DBF READ WRITE

C:\ ORACLE\ ORADATA\ TESTDB\ TOOLS01.DBF READ ONLY

C:\ ORACLE\ ORADATA\ TESTDB\ USERS01.DBF READ WRITE

C:\ ORACLE\ ORADATA\ TESTDB\ TOOLS02.DBF READ ONLY

6 rows have been selected.

SQL > startup nomount

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

SQL > CREATE CONTROLFILE REUSE DATABASE "testdb" NORESETLOGS ARCHIVELOG

2-- SET STANDBY TO MAXIMIZE PERFORMANCE

3 MAXLOGFILES 5

4 MAXLOGMEMBERS 3

5 MAXDATAFILES 100

6 MAXINSTANCES 1

7 MAXLOGHISTORY 226

8 LOGFILE

9 GROUP 1'C:\ ORACLE\ ORADATA\ testdb\ REDO01.LOG' SIZE 100m

10 GROUP 2'C:\ ORACLE\ ORADATA\ testdb\ REDO02.LOG' SIZE 100m

11 GROUP 3'C:\ ORACLE\ ORADATA\ testdb\ REDO03.LOG' SIZE 100m

12-STANDBY LOGFILE

13 DATAFILE

14'C:\ ORACLE\ ORADATA\ testdb\ SYSTEM01.DBF'

15'C:\ ORACLE\ ORADATA\ testdb\ UNDOTBS01.DBF'

16'C:\ ORACLE\ ORADATA\ testdb\ INDX01.DBF'

17'C:\ ORACLE\ ORADATA\ testdb\ TOOLS01.DBF'

18'C:\ ORACLE\ ORADATA\ testdb\ USERS01.DBF'

19 CHARACTER SET ZHS16GBK

Control file has been created

SQL > select name from v$datafile

NAME

C:\ ORACLE\ ORADATA\ TESTDB\ SYSTEM01.DBF

C:\ ORACLE\ ORADATA\ TESTDB\ UNDOTBS01.DBF

C:\ ORACLE\ ORADATA\ TESTDB\ INDX01.DBF

C:\ ORACLE\ ORADATA\ TESTDB\ TOOLS01.DBF

C:\ ORACLE\ ORADATA\ TESTDB\ USERS01.DBF

SQL > alter database open

The database has changed.

SQL > select name from v$datafile

NAME

C:\ ORACLE\ ORADATA\ TESTDB\ SYSTEM01.DBF

C:\ ORACLE\ ORADATA\ TESTDB\ UNDOTBS01.DBF

C:\ ORACLE\ ORADATA\ TESTDB\ INDX01.DBF

C:\ ORACLE\ ORADATA\ TESTDB\ TOOLS01.DBF

C:\ ORACLE\ ORADATA\ TESTDB\ USERS01.DBF

C:\ ORACLE\ ORA92\ DATABASE\ MISSING00006-- added an unrecognized file name when OPEN

6 rows have been selected.

SQL > alter database rename file 'MISSING00006' to' C:\ ORACLE\ ORA92\ DATABASE\ TOOL

S02.DBF'

Alter database rename file 'MISSING00006' to' C:\ ORACLE\ ORA92\ DATABASE\ TOOLS02.D

BF'

ERROR is on line 1:

ORA-01511: error renaming log / data file

ORA-01141: error renaming data file 6-no new file found

'C:\ ORACLE\ ORA92\ DATABASE\ TOOLS02.DBF'

ORA-01111: the name of data file 6 is unknown-please rename to correct the file

ORA-01110: data file 6:'C:\ ORACLE\ ORA92\ DATABASE\ MISSING00006'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) the system cannot find the specified file.

ERROR is on line 1:

ORA-01113:?? 4?

ORA-01110: 4:'C:\ ORACLE\ ORADATA\ TESTDB\ TOOLS01.DBF'

SQL > alter database datafile'C:\ ORACLE\ ORADATA\ TESTDB\ TOOLS01.DBF' offline

The database has changed.

SQL > alter database datafile'C:\ ORACLE\ ORADATA\ TESTDB\ TOOLS02.DBF' offline

Alter database datafile'C:\ ORACLE\ ORADATA\ TESTDB\ TOOLS02.DBF' offline

ERROR is on line 1:

ORA-01516:?,? 'C:\ ORACLE\ ORADATA\ TESTDB\ TOOLS02.DBF'

SQL > alter database open

The database has changed.

SQL > alter database datafile 'MISSING00006' offline;-- > Note that read-only tablespaces must be offline first, and note that MISSING00006 cannot write the full path

The database has changed.

SQL > alter database rename file 'MISSING00006' to' C:\ ORACLE\ ORADATA\ TESTDB\ TOO

LS02.DBF'

The database has changed.

SQL > alter tablespace tools online

The tablespace has changed.

Method 2

SQL > SHUTDOWN

The database has been closed.

The database has been uninstalled.

The ORACLE routine has been closed.

SQL > STARTUP NOMOUNT

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

SQL > CREATE CONTROLFILE REUSE DATABASE "testdb" NORESETLOGS ARCHIVELOG

2-- SET STANDBY TO MAXIMIZE PERFORMANCE

3 MAXLOGFILES 5

4 MAXLOGMEMBERS 3

5 MAXDATAFILES 100

6 MAXINSTANCES 1

7 MAXLOGHISTORY 226

8 LOGFILE

9 GROUP 1'C:\ ORACLE\ ORADATA\ testdb\ REDO01.LOG' SIZE 100m

10 GROUP 2'C:\ ORACLE\ ORADATA\ testdb\ REDO02.LOG' SIZE 100m

11 GROUP 3'C:\ ORACLE\ ORADATA\ testdb\ REDO03.LOG' SIZE 100m

12-STANDBY LOGFILE

13 DATAFILE

14'C:\ ORACLE\ ORADATA\ testdb\ SYSTEM01.DBF'

15'C:\ ORACLE\ ORADATA\ testdb\ UNDOTBS01.DBF'

16'C:\ ORACLE\ ORADATA\ testdb\ INDX01.DBF'

17'C:\ ORACLE\ ORADATA\ testdb\ TOOLS01.DBF'

18'C:\ ORACLE\ ORADATA\ testdb\ TOOLS02.DBF',-- add this data file to the creation statement

19'C:\ ORACLE\ ORADATA\ testdb\ USERS01.DBF'

20 CHARACTER SET ZHS16GBK

Control file has been created

SQL > SELECT NAME FROM V$DATAFILE

NAME

C:\ ORACLE\ ORADATA\ TESTDB\ SYSTEM01.DBF

C:\ ORACLE\ ORADATA\ TESTDB\ UNDOTBS01.DBF

C:\ ORACLE\ ORADATA\ TESTDB\ INDX01.DBF

C:\ ORACLE\ ORADATA\ TESTDB\ TOOLS01.DBF

C:\ ORACLE\ ORADATA\ TESTDB\ USERS01.DBF

C:\ ORACLE\ ORADATA\ TESTDB\ TOOLS02.DBF

6 rows have been selected.

SQL > ALTER DATABASE OPEN

ALTER DATABASE OPEN

ERROR is on line 1:

ORA-01113:?? 4?

ORA-01110: 4:'C:\ ORACLE\ ORADATA\ TESTDB\ TOOLS01.DBF'

SQL > RECOVER TABLESPACE TOOLS

Complete the media recovery.

SQL > ALTER DATABASE OPEN

The database has changed.

# #

# [1.6] restore using backup control files

# #

There are many examples before.

UID393 post 3566 essence 23 points 3803 circulation points 3803 points money 0 yuan reading rights 225online time 173hours registration time 2003-1-21 finally log in 2008-8-27 to view details

TOP

Fly115

Super moderator

Send a short message in personal space and add it as a friend. Currently, students on the 5th floor are published on 2005-3-19 18:03 only to see the author.

# # #

# [1.7] the data file is corrupted and there is no backup, no backup control file

# and other data file backups are all after the data file is created

Backup of

# # #

# [Test 1] (data file created before resetlogs), data file lost, no backup, control file corruption, backup control file, but after backup control file is rebuilt on the date of the file reconstruction

# Open the database using resetlogs, so there are no data files that have all the logs since they were created

# because the previous log is intercepted, (so there must be a backup after resetlog

# unless it is a data file created after resetlogs, which can be recovered through create datafile)

# of course, if you have the control files and related data after resetlogs, you can recover them completely, see 1.12

# # #

Restore the original data file and control file

Then SQL > startup mount

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

The database is loaded.

SQL > recover database using backup controlfile until cancel

ORA-00279: a change of 1069386 (generated at 15:47:59 at 02Accord 25Accord 2005) is required for thread 1

ORA-00289: suggestion: C:\ ORACLE\ ORA92\ RDBMS\ ARC00013.001

ORA-00280: change 1069386 is made in sequence # 13 for thread 1

Specify log: {= suggested | filename | AUTO | CANCEL}

Auto-- > > recovery using auto is also successful

ORA-00308: unable to open archive log'C:\ ORACLE\ ORA92\ RDBMS\ ARC00013.001'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) the system cannot find the specified file.

ORA-00308: unable to open archive log'C:\ ORACLE\ ORA92\ RDBMS\ ARC00013.001'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) the system cannot find the specified file.

SQL > recover database using backup controlfile until cancel;-- > this cancel indicates that every time a log is applied, it will be prompted until it ends after typing cancel

ORA-00279: a change of 1069386 (generated at 15:47:59 at 02Accord 25Accord 2005) is required for thread 1

ORA-00289: suggestion: C:\ ORACLE\ ORA92\ RDBMS\ ARC00013.001

ORA-00280: change 1069386 is made in sequence # 13 for thread 1

Specify log: {= suggested | filename | AUTO | CANCEL}

Cancel-- enter cancel to restore successfully-- > > cancel option allows you to roll forward one log file at a time, and enter cancel when you need to stop the recovery

In this way, online log files are not automatically used.

Media recovery has been cancelled.

SQL > alter database open resetlogs

The database has changed.

SQL > select name,status from v$datafile

NAME STATUS

C:\ ORACLE\ ORADATA\ TESTDB\ SYSTEM01.DBF SYSTEM

C:\ ORACLE\ ORADATA\ TESTDB\ UNDOTBS01.DBF ONLINE

C:\ ORACLE\ ORADATA\ TESTDB\ INDX01.DBF ONLINE

C:\ ORACLE\ ORADATA\ TESTDB\ TOOLS01.DBF ONLINE

C:\ ORACLE\ ORADATA\ TESTDB\ USERS01.DBF ONLINE

C:\ ORACLE\ ORADATA\ TESTDB\ TOOLS02.DBF ONLINE

6 rows have been selected.

SQL > create table arch (status varchar (2)) tablespace users

The table has been created.

SQL > alter system switch logfile

The system has changed.

SQL > insert into arch select 'ok' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

SQL > insert into arch select 'ok' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

SQL > alter system switch logfile

The system has changed.

SQL > insert into arch select 'no' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

Destroy the file USERS01.DBF with ULTRAEDIT when the database is open

SQL > shutdown abort

The ORACLE routine has been closed.

SQL > startup mount

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

ORA-01991:?'C:\ oracle\ ora92\ DATABASE\ PWDtestdb.ORA'

SQL > alter database datafile'C:\ ORACLE\ ORADATA\ TESTDB\ USERS01.DBF' offline

The database has changed.

SQL > alter database create datafile'C:\ ORACLE\ ORADATA\ TESTDB\ USERS01.DBF'

Alter database create datafile'C:\ ORACLE\ ORADATA\ TESTDB\ USERS01.DBF'

ERROR is on line 1:

ORA-01181:?? 5? RESETLOGS?

ORA-01110: 5:'C:\ ORACLE\ ORADATA\ TESTDB\ USERS01.DBF'

Because the database is opened using resetlogs, there is no data file that has all the logs since it was created, because the previous log was intercepted

(therefore, there must be a backup after resetlog, unless it is a data file created after resetlogs, which can be restored through create datafile)

In other words, because the creation time and related information of the data file at the time of creation are saved in the control file! If the control file is lost

If the information at the time of the creation of the data file is lost, the database will not know whether to start recover datafile from the time the file is created.

Which archive log needs to start. There is no way to know whether the data file can be recovered from the existing archive log

# # #

# (resetlog is before the data file is created or the control file contains the initial information for creating the data file)

Then the data files created after we test resetlogs are lost, and there is no backup recovery method.

Assuming that the previous users01 is not damaged, the database is opened

# # #

There are two kinds.

# [Test 2] Control files are normal, data files are missing, data files are lost without backup

# that is, if the missing data file is created in the control file, and the control file is not refreshed by trace, the control file contains all the information of the data file since it was created.

SQL > shutdown

ORA-01109:?

The database has been uninstalled.

The ORACLE routine has been closed.

SQL > startup

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

ORA-01991:?'C:\ oracle\ ora92\ DATABASE\ PWDtestdb.ORA'

SQL > alter database open

The database has changed.

SQL >

SQL > alter tablespace users add datafile'C:\ ORACLE\ ORADATA\ TESTDB\ USERS02.DBF'

Size 25M

The tablespace has changed.

SQL > create table arch (status varchar (2)) tablespace users

The table has been created.

SQL > alter system switch logfile

The system has changed.

SQL > insert into arch select 'ok' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

SQL > insert into arch select * from arch

6166 rows were created.

SQL > /

12332 rows were created.

SQL > /

24664 rows were created.

SQL > /

49328 rows were created.

SQL > commit

The submission is complete.

SQL > alter system switch logfile

The system has changed.

SQL > insert into arch select 'no' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

SQL > select GROUP#,SEQUENCE#,archived, STATUS from v$log

GROUP# SEQUENCE# ARC STATUS

1 5 NO CURRENT

2 3 YES INACTIVE

3 4 YES ACTIVE

SQL > select file#, name from v$datafile

FILE# NAME

1 C:\ ORACLE\ ORADATA\ TESTDB\ SYSTEM01.DBF

2 C:\ ORACLE\ ORADATA\ TESTDB\ UNDOTBS01.DBF

3 C:\ ORACLE\ ORADATA\ TESTDB\ INDX01.DBF

4 C:\ ORACLE\ ORADATA\ TESTDB\ TOOLS01.DBF

5 C:\ ORACLE\ ORADATA\ TESTDB\ USERS01.DBF

6 C:\ ORACLE\ ORADATA\ TESTDB\ USERS02.DBF

6 rows have been selected.

SQL > select segment_name from dba_extents where FILE_ID=6

SEGMENT_NAME

ARCH

SQL > shutdown abort

The ORACLE routine has been closed.

Edit the file USERS02.DBF with ultraedit

SQL > startup mount

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

ORA-01991:?'C:\ oracle\ ora92\ DATABASE\ PWDtestdb.ORA'

SQL > select name, status from v$datafile

NAME STATUS

C:\ ORACLE\ ORADATA\ TESTDB\ SYSTEM01.DBF SYSTEM

C:\ ORACLE\ ORADATA\ TESTDB\ UNDOTBS01.DBF ONLINE

C:\ ORACLE\ ORADATA\ TESTDB\ INDX01.DBF ONLINE

C:\ ORACLE\ ORADATA\ TESTDB\ TOOLS01.DBF ONLINE

C:\ ORACLE\ ORADATA\ TESTDB\ USERS01.DBF ONLINE

C:\ ORACLE\ ORADATA\ TESTDB\ USERS02.DBF ONLINE

6 rows have been selected.

SQL > alter database create datafile'C:\ ORACLE\ ORADATA\ TESTDB\ USERS02.DBF';-- online can also be create

The database has changed.

The first datafile of system tablespace is that you cannot use the alter database create datafile command

SQL > recover datafile'C:\ ORACLE\ ORADATA\ TESTDB\ USERS02.DBF'

Complete the media recovery.

SQL > alter database open

The database has changed.

SQL > select count (*), status from arch group by status

COUNT (*) ST

6166 no

98656 ok

Fully recovered.

# [Test 3] Control files are not lost, data files are lost, data files are lost and no backup

-- > > Let's see if recover database will lose data in the case of offline.

Alter tablespace users add datafile'C:\ ORACLE\ ORADATA\ TESTDB\ USERS03.DBF' size 25m

SQL > select file#,name from v$datafile

FILE# NAME

1 C:\ ORACLE\ ORADATA\ TESTDB\ SYSTEM01.DBF

2 C:\ ORACLE\ ORADATA\ TESTDB\ UNDOTBS01.DBF

3 C:\ ORACLE\ ORADATA\ TESTDB\ INDX01.DBF

4 C:\ ORACLE\ ORADATA\ TESTDB\ TOOLS01.DBF

5 C:\ ORACLE\ ORADATA\ TESTDB\ USERS01.DBF

6 C:\ ORACLE\ ORADATA\ TESTDB\ USERS02.DBF

7 C:\ ORACLE\ ORADATA\ TESTDB\ USERS03.DBF

SQL > select segment_name from dba_extents where FILE_ID= 7

No rows selected

SQL > insert into arch select * from arch

104822 rows were created.

SQL > commit

The submission is complete.

SQL > select segment_name from dba_extents where FILE_ID= 7

SEGMENT_NAME

ARCH

Use ultraedit to edit the file USERS03.DBF persecuted the file

SQL > shutdown abort

The ORACLE routine has been closed.

SQL > startup

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

ORA-01991:?'C:\ oracle\ ora92\ DATABASE\ PWDtestdb.ORA'

SQL > alter database open

Alter database open

ERROR is on line 1:

ORA-01157: 7 -? DBWR?

SQL > alter database datafile'C:\ ORACLE\ ORADATA\ TESTDB\ USERS03.DBF' offline

The database has changed.

SQL > alter database create datafile'C:\ ORACLE\ ORADATA\ TESTDB\ USERS03.DBF'

The database has changed.

SQL > recover datafile'C:\ ORACLE\ ORADATA\ TESTDB\ USERS03.DBF'

Complete the media recovery.

SQL > alter database datafile'C:\ ORACLE\ ORADATA\ TESTDB\ USERS03.DBF' online

The database has changed.

SQL > alter database open

The database has changed.

SQL > select count (*), status from arch group by status

COUNT (*) ST

12332 no

197312 ok

-- data is not lost

~ ~ ~

What if we use recover database to recover offline data files?

~ ~ ~

SQL > alter tablespace users add datafile'C:\ ORACLE\ ORADATA\ TESTDB\ USERS04.DBF' size 25m

The tablespace has changed.

SQL > select file#,name from v$datafile

FILE# NAME

1 C:\ ORACLE\ ORADATA\ TESTDB\ SYSTEM01.DBF

2 C:\ ORACLE\ ORADATA\ TESTDB\ UNDOTBS01.DBF

3 C:\ ORACLE\ ORADATA\ TESTDB\ INDX01.DBF

4 C:\ ORACLE\ ORADATA\ TESTDB\ TOOLS01.DBF

5 C:\ ORACLE\ ORADATA\ TESTDB\ USERS01.DBF

6 C:\ ORACLE\ ORADATA\ TESTDB\ USERS02.DBF

7 C:\ ORACLE\ ORADATA\ TESTDB\ USERS03.DBF

8 C:\ ORACLE\ ORADATA\ TESTDB\ USERS04.DBF

Eight rows have been selected.

SQL > insert into arch select * from arch

209644 rows were created.

SQL > select segment_name from dba_extents where FILE_ID= 8

SEGMENT_NAME

ARCH

SQL > commit

The submission is complete.

SQL >

SQL > shutdown abort

The ORACLE routine has been closed.

Delete the file USERS04

SQL > startup mount

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

ORA-01991:?'C:\ oracle\ ora92\ DATABASE\ PWDtestdb.ORA'

SQL > alter database datafile'C:\ ORACLE\ ORADATA\ TESTDB\ USERS04.DBF' offline

The database has changed.

SQL > recover datafile'C:\ ORACLE\ ORADATA\ TESTDB\ USERS04.DBF'

ORA-00283:?

ORA-01110: 8:'C:\ ORACLE\ ORADATA\ TESTDB\ USERS04.DBF'

ORA-01157:? 8 -? DBWR?

ORA-01110: 8:'C:\ ORACLE\ ORADATA\ TESTDB\ USERS04.DBF'

SQL > alter database create datafile'C:\ ORACLE\ ORADATA\ TESTDB\ USERS04.DBF'

The database has changed.

SQL > select name,status from v$datafile

NAME STATUS

C:\ ORACLE\ ORADATA\ TESTDB\ SYSTEM01.DBF SYSTEM

C:\ ORACLE\ ORADATA\ TESTDB\ UNDOTBS01.DBF ONLINE

C:\ ORACLE\ ORADATA\ TESTDB\ INDX01.DBF ONLINE

C:\ ORACLE\ ORADATA\ TESTDB\ TOOLS01.DBF ONLINE

C:\ ORACLE\ ORADATA\ TESTDB\ USERS01.DBF ONLINE

C:\ ORACLE\ ORADATA\ TESTDB\ USERS02.DBF ONLINE

C:\ ORACLE\ ORADATA\ TESTDB\ USERS03.DBF ONLINE

C:\ ORACLE\ ORADATA\ TESTDB\ USERS04.DBF RECOVER

Eight rows have been selected.

SQL > alter database datafile'C:\ ORACLE\ ORADATA\ TESTDB\ USERS04.DBF' offline

The database has changed.

SQL > recover database;-We see that the recovery of data files in offline is invalid, and we still have to recover one later after online

Complete the media recovery.

SQL > select count (*), status from arch group by status

Select count (*), status from arch group by status

ERROR is on line 1:

ORA-01219:?:?

SQL > alter database datafile'C:\ ORACLE\ ORADATA\ TESTDB\ USERS04.DBF' online

The database has changed.

SQL > alter database open

Alter database open

ERROR is on line 1:

ORA-01113:?? 8?

SQL > recover database

Complete the media recovery.

SQL > alter database open

The database has changed.

SQL > select count (*), status from arch group by status

COUNT (*) ST

24664 no

394624 ok

# [Test 4] Control file loss, data file loss, no backup, trace, let's see why trace causes the information of data file creation in the header of the control file to be modified

# unable to implement create datafile function

SQL > startup

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

ORA-01991:?'C:\ oracle\ ora92\ DATABASE\ PWDtestdb.ORA'

SQL > alter database open

The database has changed.

SQL > alter tablespace users add datafile'C:\ ORACLE\ ORADATA\ TESTDB\ USERS02.DBF'

Size 25m

The tablespace has changed.

SQL > alter database backup controlfile to trace

The database has changed.

SQL > shutdown

The database has been closed.

The database has been uninstalled.

The ORACLE routine has been closed.

SQL > startup nomount

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

SQL > CREATE CONTROLFILE REUSE DATABASE "TESTDB" NORESETLOGS ARCHIVELOG

2-- SET STANDBY TO MAXIMIZE PERFORMANCE

3 MAXLOGFILES 5

4 MAXLOGMEMBERS 3

5 MAXDATAFILES 100

6 MAXINSTANCES 1

7 MAXLOGHISTORY 226

8 LOGFILE

9 GROUP 1'C:\ ORACLE\ ORADATA\ TESTDB\ REDO01.LOG' SIZE 100m

10 GROUP 2'C:\ ORACLE\ ORADATA\ TESTDB\ REDO02.LOG' SIZE 100m

11 GROUP 3'C:\ ORACLE\ ORADATA\ TESTDB\ REDO03.LOG' SIZE 100m

12-STANDBY LOGFILE

13 DATAFILE

14'C:\ ORACLE\ ORADATA\ TESTDB\ SYSTEM01.DBF'

15'C:\ ORACLE\ ORADATA\ TESTDB\ UNDOTBS01.DBF'

16'C:\ ORACLE\ ORADATA\ TESTDB\ INDX01.DBF'

17'C:\ ORACLE\ ORADATA\ TESTDB\ TOOLS01.DBF'

18'C:\ ORACLE\ ORADATA\ TESTDB\ USERS01.DBF'

19'C:\ ORACLE\ ORADATA\ TESTDB\ USERS02.DBF'

20 CHARACTER SET ZHS16GBK

Control file has been created

SQL > alter database open

The database has changed.

SQL > shutdown

The database has been closed.

The database has been uninstalled.

The ORACLE routine has been closed.

SQL > startup mount

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

The database is loaded.

SQL > alter database create datafile'C:\ ORACLE\ ORADATA\ TESTDB\ USERS02.DBF'

Alter database create datafile'C:\ ORACLE\ ORADATA\ TESTDB\ USERS02.DBF'

ERROR is on line 1:

ORA-01178: file 6 was created before the last CREATE CONTROLFILE and cannot be recreated

ORA-01110: data file 6:'C:\ ORACLE\ ORADATA\ TESTDB\ USERS02.DBF'

~ ~ ~

Therefore, you must first make a full backup after performing create controlfile.

~ ~ ~

# [Test 5] the control file does not have the initial information to create the data file. Use different trace methods

SQL > alter database open

The database has changed.

SQL >

SQL > col name format A50

SQL > select name, status from v$datafile

NAME STATUS

C:\ ORACLE\ ORADATA\ TESTDB\ SYSTEM01.DBF SYSTEM

C:\ ORACLE\ ORADATA\ TESTDB\ UNDOTBS01.DBF ONLINE

C:\ ORACLE\ ORADATA\ TESTDB\ INDX01.DBF ONLINE

C:\ ORACLE\ ORADATA\ TESTDB\ TOOLS01.DBF ONLINE

C:\ ORACLE\ ORADATA\ TESTDB\ USERS01.DBF ONLINE

SQL > alter tablespace users add datafile'C:\ ORACLE\ ORADATA\ TESTDB\ USERS02.DBF'

Size 25m

The tablespace has changed.

SQL > create table arch (status varchar (2)) tablespace users

The table has been created.

SQL > alter system switch logfile

The system has changed.

SQL > insert into arch select 'ok' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

SQL > insert into arch select * from arch

6166 rows were created.

SQL > /

12332 rows were created.

SQL > /

24664 rows were created.

SQL > /

49328 rows were created.

SQL > commit

The submission is complete.

SQL > alter system switch logfile

The system has changed.

SQL > insert into arch select 'no' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

SQL > select GROUP#,SEQUENCE#,archived, STATUS from v$log

GROUP# SEQUENCE# ARC STATUS

1 5 NO CURRENT

2 3 YES ACTIVE

3 4 YES ACTIVE

SQL > select name, status from v$datafile

NAME STATUS

C:\ ORACLE\ ORADATA\ TESTDB\ SYSTEM01.DBF SYSTEM

C:\ ORACLE\ ORADATA\ TESTDB\ UNDOTBS01.DBF ONLINE

C:\ ORACLE\ ORADATA\ TESTDB\ INDX01.DBF ONLINE

C:\ ORACLE\ ORADATA\ TESTDB\ TOOLS01.DBF ONLINE

C:\ ORACLE\ ORADATA\ TESTDB\ USERS01.DBF ONLINE

C:\ ORACLE\ ORADATA\ TESTDB\ USERS02.DBF ONLINE

6 rows have been selected.

SQL > alter database backup controlfile to'C:\ oracle\ oradata\ testdb\ controlfile

Ak.ctl';-this control file contains the header information of USERS02

The database has changed.

SQL > alter database backup controlfile to trace

The database has changed.

Use ultraedit to destroy the file USERS02 at this time

SQL > shutdown abort

The ORACLE routine has been closed.

SQL > exit

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 disconnected

Delete 3 control files

SQL > startup nomount

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

SQL > CREATE CONTROLFILE REUSE DATABASE "TESTDB" NORESETLOGS ARCHIVELOG

2-- SET STANDBY TO MAXIMIZE PERFORMANCE

3 MAXLOGFILES 5

4 MAXLOGMEMBERS 3

5 MAXDATAFILES 100

6 MAXINSTANCES 1

7 MAXLOGHISTORY 226

8 LOGFILE

9 GROUP 1'C:\ ORACLE\ ORADATA\ TESTDB\ REDO01.LOG' SIZE 100m

10 GROUP 2'C:\ ORACLE\ ORADATA\ TESTDB\ REDO02.LOG' SIZE 100m

11 GROUP 3'C:\ ORACLE\ ORADATA\ TESTDB\ REDO03.LOG' SIZE 100m

12-STANDBY LOGFILE

13 DATAFILE

14'C:\ ORACLE\ ORADATA\ TESTDB\ SYSTEM01.DBF'

15'C:\ ORACLE\ ORADATA\ TESTDB\ UNDOTBS01.DBF'

16'C:\ ORACLE\ ORADATA\ TESTDB\ INDX01.DBF'

17'C:\ ORACLE\ ORADATA\ TESTDB\ TOOLS01.DBF'

18'C:\ ORACLE\ ORADATA\ TESTDB\ USERS01.DBF'

19'C:\ ORACLE\ ORADATA\ TESTDB\ USERS02.DBF'

20 CHARACTER SET ZHS16GBK

CREATE CONTROLFILE REUSE DATABASE "TESTDB" NORESETLOGS ARCHIVELOG

ERROR is on line 1:

ORA-01503: CREATE CONTROLFILE??

ORA-01565: C:\ ORACLE\ ORADATA\ TESTDB\ USERS02.DBF'???

ORA-27047:?

OSD-04006: ReadFile () Junior 0\, N ^ 7 (6AHX ND alter database mount)

Alter database mount

ERROR is on line 1:

ORA-01100:?

SQL > shutdown

ORA-01109:?

The database has been uninstalled.

The ORACLE routine has been closed.

SQL > startup mount

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

The database is loaded.

SQL > col name format A60

SQL > select name, status from v$datafile

NAME STATUS

C:\ ORACLE\ ORADATA\ TESTDB\ SYSTEM01.DBF SYSTEM

C:\ ORACLE\ ORADATA\ TESTDB\ UNDOTBS01.DBF RECOVER

C:\ ORACLE\ ORADATA\ TESTDB\ INDX01.DBF RECOVER

C:\ ORACLE\ ORADATA\ TESTDB\ TOOLS01.DBF RECOVER

C:\ ORACLE\ ORADATA\ TESTDB\ USERS01.DBF RECOVER

SQL > alter database create datafile'C:\ ORACLE\ ORADATA\ TESTDB\ USERS02.DBF'

Alter database create datafile'C:\ ORACLE\ ORADATA\ TESTDB\ USERS02.DBF'

ERROR is on line 1:

ORA-01516: log files, data files, or temporary files that do not exist

'C:\ ORACLE\ ORADATA\ TESTDB\ USERS02.DBF'

SQL > alter database create datafile'C:\ ORACLE\ ORADATA\ TESTDB\ USERS02.DBF' as'C

:\ ORACLE\ ORADATA\ TESTDB\ USERS02.DBF' reuse

Alter database create datafile'C:\ ORACLE\ ORADATA\ TESTDB\ USERS02.DBF' as'C:\ ORA

CLE\ ORADATA\ TESTDB\ USERS02.DBF' reuse

ERROR is on line 1:

ORA-01516: log files, data files, or temporary files that do not exist

'C:\ ORACLE\ ORADATA\ TESTDB\ USERS02.DBF'

SQL > recover database

Complete the media recovery.

SQL > alter database open

The database has changed.

SQL > select name,status from v$datafile

NAME STATUS

C:\ ORACLE\ ORADATA\ TESTDB\ SYSTEM01.DBF SYSTEM

C:\ ORACLE\ ORADATA\ TESTDB\ UNDOTBS01.DBF ONLINE

C:\ ORACLE\ ORADATA\ TESTDB\ INDX01.DBF ONLINE

C:\ ORACLE\ ORADATA\ TESTDB\ TOOLS01.DBF ONLINE

C:\ ORACLE\ ORADATA\ TESTDB\ USERS01.DBF ONLINE

C:\ ORACLE\ ORA92\ DATABASE\ MISSING00006 RECOVER

6 rows have been selected.

SQL > alter database rename file 'MISSING00006' to' C:\ ORACLE\ ORADATA\ TESTDB\ USER

S02.DBF'

Alter database rename file 'MISSING00006' to' C:\ ORACLE\ ORADATA\ TESTDB\ USERS02.D

BF'

ERROR is on line 1:

ORA-01511: error renaming log / data file

ORA-01141: error renaming data file 6-no new file found

'C:\ ORACLE\ ORADATA\ TESTDB\ USERS02.DBF'

ORA-01111: the name of data file 6 is unknown-please rename to correct the file

ORA-01110: data file 6:'C:\ ORACLE\ ORA92\ DATABASE\ MISSING00006'

ORA-27047: unable to read the title block of the file

OSD-04006: ReadFile () failed to read the file

O/S-Error: (OS 38) reaches the end of the file.

SQL > alter database datafile 'MISSING00006' offline

The database has changed.

SQL > alter database rename file 'MISSING00006' to' C:\ ORACLE\ ORADATA\ TESTDB\ USER

S02.DBF'

Alter database rename file 'MISSING00006' to' C:\ ORACLE\ ORADATA\ TESTDB\ USERS02.D

BF'

ERROR is on line 1:

ORA-01511: error renaming log / data file

ORA-01141: error renaming data file 6-no new file found

'C:\ ORACLE\ ORADATA\ TESTDB\ USERS02.DBF'

ORA-01111: the name of data file 6 is unknown-please rename to correct the file

ORA-01110: data file 6:'C:\ ORACLE\ ORA92\ DATABASE\ MISSING00006'

ORA-27047: unable to read the title block of the file

OSD-04006: ReadFile () failed to read the file

O/S-Error: (OS 38) reaches the end of the file.

The database is open, but the data file USERS02 cannot be restored because the control file cannot record the information when the file was created, so rename file 'MISSING00006' cannot be implemented.

Let's verify the relationship between the control file information and the data file header information.

# [Test 6] the data file is corrupted and there is no backup. The control file uses the backup control file, and the control file is the control file after the data file is created.

Is it okay for us to use the control files that we just backed up?

SQL > startup

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

ORA-01991:?'C:\ oracle\ ora92\ DATABASE\ PWDtestdb.ORA'

SQL >

SQL > alter database open

The database has changed.

SQL > create table arch (status varchar (2)) tablespace users

The table has been created.

SQL > alter system switch logfile

The system has changed.

SQL > insert into arch select 'ok' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

SQL > insert into arch select * from arch

6166 rows were created.

SQL > /

12332 rows were created.

SQL > /

24664 rows were created.

SQL > /

49328 rows were created.

SQL > commit

The submission is complete.

SQL > alter system switch logfile

The system has changed.

SQL > insert into arch select 'no' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

SQL > select GROUP#,SEQUENCE#,archived, STATUS from v$log

GROUP# SEQUENCE# ARC STATUS

1 5 NO CURRENT

2 3 YES ACTIVE

3 4 NO ACTIVE

SQL >

SQL > alter tablespace tools add datafile'C:\ ORACLE\ ORADATA\ TESTDB\ USERS02.DBF'

Ize 10m

The tablespace has changed.

SQL > insert into arch select 'af' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

SQL > alter system switch logfile

The system has changed.

SQL > shutdown

The database has been closed.

The database has been uninstalled.

The ORACLE routine has been closed.

SQL >

SQL > startup

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

ORA-01991:?'C:\ oracle\ ora92\ DATABASE\ PWDtestdb.ORA'

SQL > alter database open

The database has changed.

SQL > alter database backup controlfile to'c:\ control01.ctl'

The database has changed.

SQL > insert into arch select 'af' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

SQL > alter system switch logfile

The system has changed.

SQL > shutdown

The database has been closed.

The database has been uninstalled.

The ORACLE routine has been closed.

SQL >

SQL > exit

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 disconnected

C:\ Documents and Settings\ lifeng.fang >

C:\ Documents and Settings\ lifeng.fang > sqlplus "sys/sunsdl as sysdba"

SQL*Plus: Release 9.2.0.1.0-Production on Wednesday March 2 18:17:11 2005

Connected to an idle routine.

SQL > startup mount

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

ORA-01991:?'C:\ oracle\ ora92\ DATABASE\ PWDtestdb.ORA'

SQL >

SQL > alter database create datafile'C:\ ORACLE\ ORADATA\ TESTDB\ USERS02.DBF'

The database has changed.

SQL > recover database using backup controlfile

ORA-00279:? 69456? (? 03? 02? 10:26:30?

ORA-00289:?: C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_3.DBF

ORA-00280:?? 69456? 1? # 3?

Specify log: {= suggested | filename | AUTO | CANCEL}

ORA-00279:? 69563? (? 03? 02? 18:12:22?

ORA-00289:?: C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_4.DBF

ORA-00280:?? 69563? 1? # 4?

ORA-00278:? 'C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1room3.DBF'

Specify log: {= suggested | filename | AUTO | CANCEL}

ORA-00279:? 69606? (? 03? 02? 18:12:22?

ORA-00289:?: C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_5.DBF

ORA-00280:?? 69606? 1? # 5?

ORA-00278:? 'C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1room4.DBF'

Specify log: {= suggested | filename | AUTO | CANCEL}

ORA-00279:? 69672? (? 03? 02? 18:13:24?

ORA-00289:?: C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_6.DBF

ORA-00280:?? 69672? 1? # 6?

ORA-00278:? 'C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1' 5.DBF'

Specify log: {= suggested | filename | AUTO | CANCEL}

ORA-00279:? 69834? (? 03? 02? 18:15:40?

ORA-00289:?: C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_7.DBF

ORA-00280:?? 69834? 1? # 7?

ORA-00278:? 'C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1 '6.DBF'

Specify log: {= suggested | filename | AUTO | CANCEL}

ORA-00308:? 'C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1' 7.DBF'

ORA-27041:?

OSD-04002: n ^ 7 (4r?*ND alter database open resetlogs)

The database has changed.

SQL > select count (*), status from arch group by status

COUNT (*) ST

12332 af

6166 no

98656 ok

[1.8] the data file is corrupted and there is no backup. The control file uses the backup control file, and the control file is the control file before the data file is created.

See Test 1 of 1.7

# [1.9] the data file is corrupted and there is no backup. The control file uses the backup control file, and the control file is the control file after the data file is created.

SQL > startup

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

ORA-01991:?'C:\ oracle\ ora92\ DATABASE\ PWDtestdb.ORA'

SQL >

SQL > alter database open

The database has changed.

SQL > create table arch (status varchar (2)) tablespace users

The table has been created.

SQL > alter system switch logfile

The system has changed.

SQL > insert into arch select 'ok' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

SQL > insert into arch select * from arch

6166 rows were created.

SQL > /

12332 rows were created.

SQL > /

24664 rows were created.

SQL > /

49328 rows were created.

SQL > commit

The submission is complete.

SQL > alter system switch logfile

The system has changed.

SQL > insert into arch select 'no' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

SQL > select GROUP#,SEQUENCE#,archived, STATUS from v$log

GROUP# SEQUENCE# ARC STATUS

1 5 NO CURRENT

2 3 YES ACTIVE

3 4 NO ACTIVE

SQL >

SQL > alter tablespace tools add datafile'C:\ ORACLE\ ORADATA\ TESTDB\ USERS02.DBF'

Ize 10m

The tablespace has changed.

SQL > insert into arch select 'af' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

SQL > alter system switch logfile;-be sure to file

The system has changed.

SQL > shutdown

The database has been closed.

The database has been uninstalled.

The ORACLE routine has been closed.

SQL >

SQL > startup

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

ORA-01991:?'C:\ oracle\ ora92\ DATABASE\ PWDtestdb.ORA'

SQL > alter database open

The database has changed.

SQL > alter database backup controlfile to'c:\ control01.ctl';-- this control file has data file header information

The database has changed.

SQL > insert into arch select 'af' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

SQL > alter system switch logfile

The system has changed.

SQL > shutdown

The database has been closed.

The database has been uninstalled.

The ORACLE routine has been closed.

SQL >

SQL > exit

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 disconnected

C:\ Documents and Settings\ lifeng.fang >

Simulation control file destruction, delete control files, restore backup control files, restore all old data files, out of users02.dbf

C:\ Documents and Settings\ lifeng.fang > sqlplus "sys/sunsdl as sysdba"

SQL*Plus: Release 9.2.0.1.0-Production on Wednesday March 2 18:17:11 2005

Connected to an idle routine.

SQL > startup mount

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

ORA-01991:?'C:\ oracle\ ora92\ DATABASE\ PWDtestdb.ORA'

SQL >

SQL > alter database create datafile'C:\ ORACLE\ ORADATA\ TESTDB\ USERS02.DBF'

The database has changed.

SQL > recover database using backup controlfile

ORA-00279:? 69456? (? 03? 02? 10:26:30?

ORA-00289:?: C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_3.DBF

ORA-00280:?? 69456? 1? # 3?

Specify log: {= suggested | filename | AUTO | CANCEL}

ORA-00279:? 69563? (? 03? 02? 18:12:22?

ORA-00289:?: C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_4.DBF

ORA-00280:?? 69563? 1? # 4?

ORA-00278:? 'C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1room3.DBF'

Specify log: {= suggested | filename | AUTO | CANCEL}

ORA-00279:? 69606? (? 03? 02? 18:12:22?

ORA-00289:?: C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_5.DBF

ORA-00280:?? 69606? 1? # 5?

ORA-00278:? 'C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1room4.DBF'

Specify log: {= suggested | filename | AUTO | CANCEL}

ORA-00279:? 69672? (? 03? 02? 18:13:24?

ORA-00289:?: C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_6.DBF

ORA-00280:?? 69672? 1? # 6?

ORA-00278:? 'C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1' 5.DBF'

Specify log: {= suggested | filename | AUTO | CANCEL}

ORA-00279:? 69834? (? 03? 02? 18:15:40?

ORA-00289:?: C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_7.DBF

ORA-00280:?? 69834? 1? # 7?

ORA-00278:? 'C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1 '6.DBF'

Specify log: {= suggested | filename | AUTO | CANCEL}

ORA-00308:? 'C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1' 7.DBF'

ORA-27041:?

OSD-04002: n ^ 7 (4r?*ND alter database open resetlogs)

The database has changed.

SQL > select count (*), status from arch group by status

COUNT (*) ST

12332 af

6166 no

98656 ok

# [1.10] the data file is corrupted and there is no backup, no backup control file, and all other data file backups are backed up after the data file is created

# [1.11] corrupt a single control file

# overwrite a good control file over a damaged control file

# [1.12] the current control file is used as a backup to control file recovery, which is restored when there is business data in the current online log (usually it doesn't make much sense, just want to do a resetlogs)

SQL > startup

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

ORA-01991:?'C:\ oracle\ ora92\ DATABASE\ PWDtestdb.ORA'

SQL >

SQL > alter database open

The database has changed.

SQL > create table arch (status varchar (2)) tablespace users

The table has been created.

SQL > alter system switch logfile

The system has changed.

SQL > insert into arch select 'ok' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

SQL > insert into arch select * from arch

6166 rows were created.

SQL > /

12332 rows were created.

SQL > /

24664 rows were created.

SQL > /

49328 rows were created.

SQL > commit

The submission is complete.

SQL > alter system switch logfile

The system has changed.

SQL > insert into arch select 'no' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

SQL > select GROUP#,SEQUENCE#,archived, STATUS from v$log

GROUP# SEQUENCE# ARC STATUS

1 5 NO CURRENT

2 3 YES ACTIVE

3 4 NO ACTIVE

SQL >

SQL > select count (*), status from arch group by status

COUNT (*) ST

6166 no

98656 ok

SQL > shutdown abort

The ORACLE routine has been closed.

SQL > startup mount

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

ORA-01991:?'C:\ oracle\ ora92\ DATABASE\ PWDtestdb.ORA'

SQL > recover database using backup controlfile

ORA-00279:?? 69399? (? 03? 15:58:22?

ORA-00289:?: C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_4.DBF

ORA-00280:?? 69399? 1? # 4?

Specify log: {= suggested | filename | AUTO | CANCEL}

Auto

ORA-00279:?? 69444? (? 03? 15:58:23?

ORA-00289:?: C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_5.DBF

ORA-00280:?? 69444? 1? # 5?

ORA-00278:? 'C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1room4.DBF'

ORA-00308:? 'C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1' 5.DBF'

ORA-27041:?

OSD-04002: n ^ 7 (4r?*ND shutdown)

ORA-01109:?

The database has been uninstalled.

The ORACLE routine has been closed.

SQL > startup mount

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

ORA-01991:?'C:\ oracle\ ora92\ DATABASE\ PWDtestdb.ORA'

SQL > recover database using backup controlfile until cancel

ORA-00279:?? 69444? (? 03? 15:58:23?

ORA-00289:?: C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_5.DBF

ORA-00280:?? 69444? 1? # 5?

Specify log: {= suggested | filename | AUTO | CANCEL}

Cancel

ORA-01547:??: RECOVER? OPEN RESETLOGS?

ORA-01194:?? 1?

ORA-01110: 1:'C:\ ORACLE\ ORADATA\ TESTDB\ SYSTEM01.DBF'

ORA-01112:?

SQL > alter database open resetlogs

Alter database open resetlogs

ERROR is on line 1:

ORA-01194:?? 1?

ORA-01110: 1:'C:\ ORACLE\ ORADATA\ TESTDB\ SYSTEM01.DBF'

SQL > shutdown

ORA-01109:?

The database has been uninstalled.

The ORACLE routine has been closed.

Here we see that it is shutdown abort, so we need online log recovery, but using backup to control file recovery does not apply online log.

If it's not shutdown abort, you can open it, because the data file is always complete.

UID393 post 3566 essence 23 points 3803 circulation points 3803 points money 0 yuan reading rights 225online time 173hours registration time 2003-1-21 finally log in 2008-8-27 to view details

TOP

Fly115

Super moderator

Send a short message in personal space and add it as a friend. Currently, students on the 6th floor are published on 2005-3-19 18:04 only to see the author.

# [1.13] how RMAN backups before resetlogs are applied to those after resetlogs

SQL > alter database open

The database has changed.

SQL > select count (*) from arch

Select count (*) from arch

ERROR is on line 1:

ORA-00942:?

SQL > create table arch (status varchar (2)) tablespace users

The table has been created.

SQL > alter system switch logfile

The system has changed.

SQL > insert into arch select 'ok' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

SQL > insert into arch select * from arch

6166 rows were created.

SQL > /

12332 rows were created.

SQL > /

24664 rows were created.

SQL > /

49328 rows were created.

SQL > commit

The submission is complete.

SQL > alter system switch logfile

The system has changed.

SQL > /

The system has changed.

SQL > /

The system has changed.

SQL > /

The system has changed.

SQL > insert into arch select 'no' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

SQL > alter system switch logfile

The system has changed.

SQL > shutdown

The database has been closed.

The database has been uninstalled.

The ORACLE routine has been closed.

-- > > simulate a resetlogs

SQL > startup mount

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

The database is loaded.

SQL > recover database until cancel

Complete the media recovery. -- > > here we can notice later.

SQL > alter database open resetlogs

The database has changed.

SQL > select count (*), status from arch group by status

COUNT (*) ST

6166 no

98656 ok

SQL > insert into arch select 'af' from dba_objects

6166 rows were created.

SQL > alter system switch logfile

The system has changed.

SQL > archive log list

Database log mode archive mode

Automatic archiving enabled

Archive end C:\ oracle\ oradata\ testdb\ archive

The earliest summary log sequence 1

Next archive log sequence 2

Current log sequence 2

SQL > shutdown

ORA-01097: cannot be closed during a transaction-commit or return first

SQL > commit

The submission is complete.

SQL > shutdown

The database has been closed.

The database has been uninstalled.

The ORACLE routine has been closed.

-- > at this time the simulation deletes the users01.dbf

We have no backup after resetlogs. We want to restore the data files backed up before resetlogs to the state of the database after resetlogs.

Key steps. Data files and control files backed up before resetlogs are restored, excluding online logs (we can see later that online logs include SCN 69628-69459)

SQL > select resetlogs_change#-1 from v$database

RESETLOGS_CHANGE#-1

68658

SQL > recover database using backup controlfile until change 69639

ORA-00279: a change of 69257 (generated at 11:08:18 at 02Universe 28 ORA-00279) is required for thread 1

ORA-00289: suggestion: C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_3.DBF

ORA-00280: change 69257 is made in sequence # 3 for thread 1

Specify log: {= suggested | filename | AUTO | CANCEL}

ORA-00279: a change of 69582 (generated at 10:28:34 in 03Universe 02) is required for thread 1

ORA-00289: suggestion: C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_4.DBF

ORA-00280: change 69582 is made in sequence # 4 for thread 1

ORA-00278: the log file'C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE is no longer required for this restore

Specify log: {= suggested | filename | AUTO | CANCEL}

ORA-00279: a change of 69628 (generated at 10:28:35 in 03Universe 02) is required for thread 1

ORA-00289: suggestion: C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_5.DBF

ORA-00280: change 69628 is made in sequence # 5 for thread 1

ORA-00278: log file'C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1room4.DBF' is no longer required for this restore

Specify log: {= suggested | filename | AUTO | CANCEL}

ORA-00308: unable to open archive log'C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1' 5.DBF'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) the system cannot find the specified file.

SQL > recover database using backup controlfile until change 69639

ORA-00279: a change of 69628 (generated at 10:28:35 in 03Universe 02) is required for thread 1

ORA-00289: suggestion: C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_5.DBF

ORA-00280: change 69628 is made in sequence # 5 for thread 1

Specify log: {= suggested | filename | AUTO | CANCEL}

Cancel

Media recovery has been cancelled.

SQL > select CONTROLFILE_CHANGE# from v$database

CONTROLFILE_CHANGE#

69628

We saw that we did not restore to 69639, because I did not restore the online log backup before resetlogs, so I could not roll forward 69628 to 69639.

SQL > shutdown

ORA-01109: the database is not open

The database has been uninstalled.

The ORACLE routine has been closed.

-- > > the control file after resetlogs is restored here

SQL > startup mount

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

SQL > select resetlogs_change#-1 control FILECTROLFILER CHANGE # from v$database

RESETLOGS_CHANGE# CONTROLFILE_CHANGE#

69639 69795

SQL > recover database

ORA-00283: the recovery session was cancelled due to an error

ORA-01190: control file or data file 1 comes from before the last RESETLOGS

ORA-01110: data file 1:'C:\ ORACLE\ ORADATA\ TESTDB\ SYSTEM01.DBF'

Then we can only make an incomplete recovery.

SQL > recover database until cancel

ORA-00279:?? 69452? (? 03? 17:45:23?

ORA-00289:?: C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_9.DBF

ORA-00280:?? 69452? 1? # 9?

Specify log: {= suggested | filename | AUTO | CANCEL}

Cancel

Media recovery has been cancelled.

SQL > recover database using backup controlfile until cancel

ORA-00279:?? 69452? (? 03? 17:45:23?

ORA-00289:?: C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_9.DBF

ORA-00280:?? 69452? 1? # 9?

Specify log: {= suggested | filename | AUTO | CANCEL}

Cancel

Media recovery has been cancelled.

SQL > select count (*), status from arch group by status

Select count (*), status from arch group by status

ERROR is on line 1:

ORA-01219:?:?

SQL > alter database open resetlogs

The database has changed.

SQL > select count (*), status from arch group by status

COUNT (*) ST

6166 no

98656 ok-- We don't see the af status

We see that online log recovery is required here and cannot be recovered over the resetlogs.

Okay, so if we restore the online log in the key step, can we go beyond 69628 to 69639 to 69795?

SQL > startup

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

The database is loaded.

The database is already open.

SQL > select count (*) from arch

Select count (*) from arch

ERROR is on line 1:

ORA-00942: table or view does not exist

SQL > create table arch (status varchar (2)) tablespace users

The table has been created.

SQL > alter system switch logfile

The system has changed.

SQL > insert into arch select 'ok' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

SQL > insert into arch select * from arch

6166 rows were created.

SQL > /

12332 rows were created.

SQL > /

24664 rows were created.

SQL > /

49328 rows were created.

SQL > commit

The submission is complete.

SQL > alter system switch logfile

The system has changed.

SQL > insert into arch select 'no' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

SQL > select GROUP#,SEQUENCE#,archived, STATUS from v$log

GROUP# SEQUENCE# ARC STATUS

1 5 NO CURRENT

2 3 YES ACTIVE

3 4 NO ACTIVE

SQL >

SQL > shutdown

The database has been closed.

The database has been uninstalled.

The ORACLE routine has been closed.

SQL >

SQL > startup mount

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

The database is loaded.

SQL > recover database until cancel

Complete the media recovery.

SQL > shutdown immediate

ORA-01109: the database is not open

The database has been uninstalled.

The ORACLE routine has been closed.

SQL > startup mount

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

The database is loaded.

SQL > select resetlogs_change#,CONTROLFILE_CHANGE# from v$database

RESETLOGS_CHANGE# CONTROLFILE_CHANGE#

68659 69884

SQL > alter database open

Alter database open

ERROR is on line 1:

ORA-01589: to open the database, you must use the RESETLOGS or NORESETLOGS option

SQL > alter database open resetlogs

The database has changed.

SQL > select resetlogs_change#,CONTROLFILE_CHANGE# from v$database

RESETLOGS_CHANGE# CONTROLFILE_CHANGE#

69889 69932

SQL > insert into arch select 'af' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

SQL > shutdown

The database has been closed.

The database has been uninstalled.

The ORACLE routine has been closed.

SQL > startup mount

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

The database is loaded.

SQL >

SQL > recover database using backup controlfile until change 69888;-- > here we see that until change 69888 is connected to RESETLOGS_CHANGE# because we think we have a backup online log.

ORA-00279: a change of 69888 (generated at 11:11:40 in 03Universe 02) is required for thread 1

ORA-00289: suggestion: C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_5.DBF

ORA-00280: change 69888 is made in sequence # 5 for thread 1

Specify log: {= suggested | filename | AUTO | CANCEL}

ORA-00308: unable to open archive log'C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1' 5.DBF'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) the system cannot find the specified file.

SQL > select resetlogs_change#,CONTROLFILE_CHANGE# from v$database

RESETLOGS_CHANGE# CONTROLFILE_CHANGE#

68659 69888

SQL >

SQL > shutdown

ORA-01109: the database is not open

The database has been uninstalled.

The ORACLE routine has been closed.

-- > > here we restore the backup of the control files after resetlogs (excluding the online logs after resetlogs)

SQL > startup mount

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

The database is loaded.

SQL > select resetlogs_change#,CONTROLFILE_CHANGE# from v$database

RESETLOGS_CHANGE# CONTROLFILE_CHANGE#

69889 69932

SQL > recover database

ORA-00283: the recovery session was cancelled due to an error

ORA-00314: log 1 (thread 1), expected serial number 1 and 5 do not match

ORA-00312: online log 1 thread 1:'C:\ ORACLE\ ORADATA\ TESTDB\ REDO01.LOG'-- > We see here that the restore is still up to 69889, but not to 69932.

The reason is that we also need to back up our online logs after resetlogs.

# [Test 1] Let's take a look at the data files, control files and online logs in front of resetlogs.

# then, if the online logs of the control files after resetlogs are backed up, is it possible to pass the resetlogs?

SQL > shutdown

The database has been closed.

The database has been uninstalled.

The ORACLE routine has been closed.

SQL > startup

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

The database is loaded.

The database is already open.

SQL > create table arch (status varchar (2)) tablespace users

The table has been created.

SQL > alter system switch logfile

The system has changed.

SQL > insert into arch select 'ok' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

SQL > insert into arch select * from arch

6166 rows were created.

SQL > /

12332 rows were created.

SQL > /

24664 rows were created.

SQL > /

49328 rows were created.

SQL > commit

The submission is complete.

SQL > alter system switch logfile

The system has changed.

SQL > insert into arch select 'no' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

SQL > select GROUP#,SEQUENCE#,archived, STATUS from v$log

GROUP# SEQUENCE# ARC STATUS

1 5 NO CURRENT

2 3 YES ACTIVE

3 4 NO ACTIVE

SQL > shutdown

The database has been closed.

The database has been uninstalled.

The ORACLE routine has been closed.

SQL >

SQL > startup mount

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

The database is loaded.

-- > > simulate resetlogs

SQL > recover database until cancel

Complete the media recovery.

SQL > shutdown

ORA-01109: the database is not open

The database has been uninstalled.

The ORACLE routine has been closed.

Here we back up all the data files, control files and online logs.

SQL > startup

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

The database is loaded.

ORA-01589: to open the database, you must use the RESETLOGS or NORESETLOGS option

SQL > alter database open resetlogs

The database has changed.

SQL > select resetlogs_change#-1 control FILECTROLFILER CHANGE # from v$database

RESETLOGS_CHANGE# CONTROLFILE_CHANGE#

69808 69850

SQL > insert into arch select 'af' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

SQL > shutdown

The database has been closed.

The database has been uninstalled.

The ORACLE routine has been closed.

At this point we back up the control files and online logs because we need 69808 to 69850 of the data

-- > > then we delete the users01.dbf

-- > > all data files, control files and online logs before restoring resetlogs

SQL > startup mount

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

The database is loaded.

SQL >

SQL > select resetlogs_change#,CONTROLFILE_CHANGE# from v$database

RESETLOGS_CHANGE# CONTROLFILE_CHANGE#

68659 69804

SQL > recover database using backup controlfile until change 69808

ORA-00279: a change of 69808 (generated at 11:35:33 in 03Universe 02) is required for thread 1

ORA-00289: suggestion: C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_5.DBF

ORA-00280: change 69808 is made in sequence # 5 for thread 1

Specify log: {= suggested | filename | AUTO | CANCEL}

ORA-00308: unable to open archive log'C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1' 5.DBF'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) the system cannot find the specified file.

SQL > recover database using backup controlfile until change 69808

ORA-00279: a change of 69808 (generated at 11:35:33 in 03Universe 02) is required for thread 1

ORA-00289: suggestion: C:\ ORACLE\ ORADATA\ TESTDB\ ARCHIVE\ 1_5.DBF

ORA-00280: change 69808 is made in sequence # 5 for thread 1

Specify log: {= suggested | filename | AUTO | CANCEL}

Cancel

Media recovery has been cancelled.

SQL > select resetlogs_change#,CONTROLFILE_CHANGE# from v$database

RESETLOGS_CHANGE# CONTROLFILE_CHANGE#-- has been applied to 69808

68659 69808

SQL >

SQL > shutdown

ORA-01109: the database is not open

-- > > well, then we need to apply 69808 to 69850 of the data.

-- > > at this time, if we want to recover 69808 to 69850 of the data, we must need the control file after resetlogs.

We can see from the previous CONTROLFILE_CHANGE# and ESETLOGS_CHANGE# that only he can fully recover to 69850.

-- > > Control files and online log files after restoring resetlogs

The database has been uninstalled.

The ORACLE routine has been closed.

SQL > startup mount

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

The database is loaded.

SQL > select resetlogs_change#,CONTROLFILE_CHANGE# from v$database

RESETLOGS_CHANGE# CONTROLFILE_CHANGE#

69809 69850

SQL > recover database

Complete the media recovery.

SQL > alter database open

The database has changed.

SQL > select count (*), status from arch group by status

COUNT (*) ST

6166 af

6166 no

98656 ok

This time it really crossed the resetlogs and made a full recovery.

-- > > pay attention to whether the serial number of the switching archive is the current serial number.

Must use TEMPORARY (some data files are not available for checkpoints) or IMMEDIATE (all data files pair

Checkpoint is not available) option. Which data files need to be restored (data file recovery or data file recovery) before tablespace reproduction comes online

Tablespace recovery). If a database crash occurs before coming back online after the file is repaired, and the crash requires that no

If the media is fully restored, the RESETLOGS option that is later used to restore the database will invalidate the offline tablespace. Restore

The only way to restore the tablespace is to restore the previous backup to another machine and use the archived log to perform the recovery.

This obviously wastes a lot of time and resources until the database fails. So before you recover, you should keep in mind

Check the v$datafile to make sure that all key tablespaces are online. However, allow data files for read-only tablespaces or through the

NORMAL options offline tablespaces, remain the same (because they are already consistent, do not want them to belong to the restore

Operation).

# [2]. Recovery in non-archived mode (rman and regular recovery) #

Preparatory work

SQL > shutdown

The database has been closed.

The database has been uninstalled.

The ORACLE routine has been closed.

SQL > startup mount

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

The database is loaded.

SQL > alter database noarchivelog

The database has changed.

SQL > alter database open

The database has changed.

SQL >

SQL > archive log list

Database log mode non-archiving mode

Automatic archiving enabled

Archive end C:\ oracle\ oradata\ testdb\ archive

The earliest summary log sequence 0

Current log sequence 1

SQL >

SQL > shutdown

The database has been closed.

The database has been uninstalled.

The ORACLE routine has been closed.

SQL > startup-- > > is started by spfile, so non-archive mode has already been started without modifying initialization parameters.

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

The database is loaded.

The database is already open.

SQL > archive log list

Database log mode non-archiving mode

Automatic archiving enabled

Archive end C:\ oracle\ oradata\ testdb\ archive

The earliest summary log sequence 0

Current log sequence 1

SQL >

# # #

# [Test 1] the database is in non-archive mode and the database is open

# # #

SQL > startup-- > > is started by spfile, so non-archive mode has already been started without modifying initialization parameters.

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

The database is loaded.

The database is already open.

SQL > archive log list

Database log mode non-archiving mode

Automatic archiving enabled

Archive end C:\ oracle\ oradata\ testdb\ archive

The earliest summary log sequence 0

Current log sequence 1

SQL >

C:\ Documents and Settings\ lifeng.fang > rman nocatalog target "sys/sunsdl"

Recovery Manager: version 9.2.0.1.0-Production

Connect to the target database: TESTDB (DBID=2334768642)

Replacing recovery directory with target database control file

RMAN > show all

The RMAN configuration parameters are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO'% favored; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 1; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO'C:\ ORACLE\ ORA92\ DATABASE\ SNCFTESTDB.ORA'

; # default

RMAN > backup database include current controlfile

Launch backup in 03-March-05

Assigned channel: ORA_DISK_1

Channel ORA_DISK_1: sid=16 devtype=DISK

Channel ORA_DISK_1: starting backup set of full data files

Channel ORA_DISK_1: specifying data files in the backup set

Include the current SPFILE in the backup set

The backup set includes the current control file

RMAN-00571: =

RMAN-00569: = ERROR MESSAGE STACK FOLLOWS =

RMAN-00571: =

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 03/03/2005 09:46:

ORA-19602: active files cannot be backed up or copied in NOARCHIVELOG mode

~ ~

We see that if it's NOARCHIVELOG mode,

You cannot make a RMAN backup if the database is open

~ ~

~ ~ ~

We can use RMAN to copy control files.

RMAN > copy CURRENT CONTROLFILE TO'c:\ cf.cpy'

Launch copy in 03-March-05

Assigned channel: ORA_DISK_1

Channel ORA_DISK_1: sid=16 devtype=DISK

Channel ORA_DISK_1: current control file copied

Output file name = C:\ CF.CPY

Completion of copy in 03-March-05

~ ~ ~

# # #

# [Test 2] Database in non-archived mode, database MOUNT, backup the entire database

# # #

SQL > shutdown

The database has been closed.

The database has been uninstalled.

The ORACLE routine has been closed.

SQL > startup mount

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

The database is loaded.

SQL >

RMAN > backup database include current controlfile

Launch backup in 03-March-05

Assigned channel: ORA_DISK_1

Channel ORA_DISK_1: sid=14 devtype=DISK

Channel ORA_DISK_1: starting backup set of full data files

Channel ORA_DISK_1: specifying data files in the backup set

Include the current SPFILE in the backup set

The backup set includes the current control file

Enter the data file fno=00001 name=C:\ ORACLE\ ORADATA\ TESTDB\ SYSTEM01.DBF

Enter the data file fno=00002 name=C:\ ORACLE\ ORADATA\ TESTDB\ UNDOTBS01.DBF

Enter the data file fno=00003 name=C:\ ORACLE\ ORADATA\ TESTDB\ INDX01.DBF

Enter the data file fno=00005 name=C:\ ORACLE\ ORADATA\ TESTDB\ USERS01.DBF

Enter the data file fno=00004 name=C:\ ORACLE\ ORADATA\ TESTDB\ TOOLS01.DBF

Enter the data file fno=00006 name=C:\ ORACLE\ ORADATA\ TESTDB\ USERS02.DBF

Channel ORA_DISK_1: starting segment 1 in 03-March-05

Channel ORA_DISK_1: completed segment 1 in 03-March-05

Segment handle=C:\ ORACLE\ ORA92\ DATABASE\ 02GECED4_1_1 comment=NONE

Channel ORA_DISK_1: backup set completed, elapsed time: 00:00:27

Completion of backup in 03-March-05

RMAN > backup tablespace users

Launch backup in 03-March-05

Use channel ORA_DISK_1

Channel ORA_DISK_1: starting backup set of full data files

Channel ORA_DISK_1: specifying data files in the backup set

Enter the data file fno=00005 name=C:\ ORACLE\ ORADATA\ TESTDB\ USERS01.DBF

Channel ORA_DISK_1: starting segment 1 in 03-March-05

Channel ORA_DISK_1: completed segment 1 in 03-March-05

Segment handle=C:\ ORACLE\ ORA92\ DATABASE\ 03GECEG5_1_1 comment=NONE

Channel ORA_DISK_1: backup set completed, elapsed time: 00:00:01

Completion of backup in 03-March-05

# # #

# [Test 3] Database in non-archived mode, database MOUNT, backup tablespace

# # #

RMAN > backup tablespace users

Launch backup in 03-March-05

Use channel ORA_DISK_1

Channel ORA_DISK_1: starting backup set of full data files

Channel ORA_DISK_1: specifying data files in the backup set

Enter the data file fno=00005 name=C:\ ORACLE\ ORADATA\ TESTDB\ USERS01.DBF

Channel ORA_DISK_1: starting segment 1 in 03-March-05

Channel ORA_DISK_1: completed segment 1 in 03-March-05

Segment handle=C:\ ORACLE\ ORA92\ DATABASE\ 03GECEG5_1_1 comment=NONE

Channel ORA_DISK_1: backup set completed, elapsed time: 00:00:01

Completion of backup in 03-March-05

Note that tablespaces are logical concepts, regular backups

You must open the database to see the tablespace

But RMAN can back up tablespaces under mount

# # #

# [Test 4] Database in non-archived mode, database MOUNT, backup tablespace is regular OFFLINE

# # #

~ ~ ~

If you offline a tablespace using the TEMPORARY option, the database will still take the tablespace

To make a checkpoint for all files in which if a file IO is corrupted, then he takes a look at the file

The checkpoint is not performed, but if the normal mode is offline, the checkpoint is forced to be written, so if

If the io is physically damaged, then the NORMAL mode may not be offline. Assume that the tablespace has two files

T 1 T 2 T 2 is offline if T 1 is miswritten. If you use the temporary option, ORACLE is making T1

Set up a checkpoint check file D2 before offline.

~ ~ ~

SQL > shutdown

The database has been closed.

The database has been uninstalled.

The ORACLE routine has been closed.

SQL > startup

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

The database is loaded.

The database is already open.

SQL > alter tablespace users offline immediate

Alter tablespace users offline immediate

ERROR is on line 1:

ORA-01145: emergency offline is not allowed unless media recovery is enabled

SQL > alter tablespace users offline TEMPORARY

The tablespace has changed.

SQL > shutdown

The database has been closed.

The database has been uninstalled.

The ORACLE routine has been closed.

SQL > startup mount

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

The database is loaded.

RMAN > backup tablespace users

Launch backup in 03-March-05

Assigned channel: ORA_DISK_1

Channel ORA_DISK_1: sid=14 devtype=DISK

Channel ORA_DISK_1: starting backup set of full data files

Channel ORA_DISK_1: specifying data files in the backup set

Enter the data file fno=00005 name=C:\ ORACLE\ ORADATA\ TESTDB\ USERS01.DBF

Channel ORA_DISK_1: starting segment 1 in 03-March-05

Channel ORA_DISK_1: completed segment 1 in 03-March-05

Segment handle=C:\ ORACLE\ ORA92\ DATABASE\ 09GECGJ0_1_1 comment=NONE

Channel ORA_DISK_1: backup set completed, elapsed time: 00:00:03

Completion of backup in 03-March-05

SQL > alter database open

The database has changed.

SQL > insert into arch select 'af' from dba_objects

Insert into arch select 'af' from dba_objects

ERROR is on line 1:

ORA-00376: cannot read file 5 at this time

ORA-01110: data file 5:'C:\ ORACLE\ ORADATA\ TESTDB\ USERS01.DBF'

SQL > alter tablespace users online

The tablespace has changed.

SQL > insert into arch select 'af' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

SQL > alter tablespace users offline TEMPORARY

The tablespace has changed.

SQL > shutdown

The database has been closed.

The database has been uninstalled.

The ORACLE routine has been closed.

SQL > startup mount

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

The database is loaded.

RMAN > backup tablespace users

Launch backup in 03-March-05

Assigned channel: ORA_DISK_1

Channel ORA_DISK_1: sid=14 devtype=DISK

Channel ORA_DISK_1: starting backup set of full data files

Channel ORA_DISK_1: specifying data files in the backup set

Enter the data file fno=00005 name=C:\ ORACLE\ ORADATA\ TESTDB\ USERS01.DBF

Channel ORA_DISK_1: starting segment 1 in 03-March-05

Channel ORA_DISK_1: completed segment 1 in 03-March-05

Segment handle=C:\ ORACLE\ ORA92\ DATABASE\ 0AGECGNM_1_1 comment=NONE

Channel ORA_DISK_1: backup set completed, elapsed time: 00:00:03

Completion of backup in 03-March-05

# # #

# [Test 5] Database in non-archived mode, database MOUNT,shutdown abort, backup tablespace

# # #

SQL > alter tablespace users online

The tablespace has changed.

SQL > alter system switch logfile

The system has changed.

SQL > insert into arch select 'ok' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

SQL > insert into arch select * from arch

123320 rows were created.

SQL > /

246640 rows were created.

SQL > /

493280 rows were created.

SQL > /

986560 rows were created.

SQL > commit

The submission is complete.

SQL > alter system switch logfile

The system has changed.

SQL > insert into arch select 'no' from dba_objects

6166 rows were created.

SQL > commit

The submission is complete.

SQL > select GROUP#,SEQUENCE#,archived, STATUS from v$log

GROUP# SEQUENCE# ARC STATUS

1 2 NO ACTIVE

2 1 NO ACTIVE

3 3 NO CURRENT

SQL > shutdown abort

The ORACLE routine has been closed.

SQL >

SQL > startup mount

The ORACLE routine has been started.

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

After reading this article, I believe you have a certain understanding of "how to achieve backup and recovery in RMAN". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.