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

This article introduces what is the handling steps of abnormal power outage and control file failure of ORACLE server. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

Here is just a simple record of the steps to make a memo.

I. version

SQL > select * from v$version

BANNER

Oracle Database 11g Enterprise Edition Release 11.2.0.1. 0-Production

PL/SQL Release 11.2.0.1. 0-Production CORE 11.2.0.1

Second, failure

SQL > startup mount

The ORACLE routine has been started.

Total System Global Area 1046224896 bytes

Fixed Size 1379212 bytes

Variable Size 708838516 bytes

Database Buffers 331350016 bytes

Redo Buffers 4657152 bytes

The database is loaded.

SQL > alter dabase open

Alter dabase open

*

An error occurred on line 1:

ORA-00940: invalid ALTER command

SQL > alter database open

Alter database open

*

An error occurred on line 1:

ORA-01122: verification of database file 1 failed

ORA-01110: data file 1:'G:\ APP\ ADMINISTRATOR\ ORADATA\ ORCL\ SYSTEM01.DBF'

ORA-01207: files are newer than control files-old control files

Third, processing steps

SQL > alter database backup controlfile to trace as'G:\ ctl1.txt'

The database has changed.

SQL > shutdown immediate

ORA-01109: the database is not open

The database has been uninstalled.

The ORACLE routine has been closed.

SQL > startup nomount

The ORACLE routine has been started.

Total System Global Area 1046224896 bytes

Fixed Size 1379212 bytes

Variable Size 708838516 bytes

Database Buffers 331350016 bytes

Redo Buffers 4657152 bytes

SQL > CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS NOARCHIVELOG

2 MAXLOGFILES 16

3 MAXLOGMEMBERS 3

4 MAXDATAFILES 100

5 MAXINSTANCES 8

6 MAXLOGHISTORY 292

7 LOGFILE

8 GROUP 1'G:\ APP\ ADMINISTRATOR\ ORADATA\ ORCL\ REDO01.LOG' SIZE 50m BLOCKSIZ

E 512

9 GROUP 2'G:\ APP\ ADMINISTRATOR\ ORADATA\ ORCL\ REDO02.LOG' SIZE 50m BLOCKSIZ

E 512

10 GROUP 3'G:\ APP\ ADMINISTRATOR\ ORADATA\ ORCL\ REDO03.LOG' SIZE 50m BLOCKSIZ

E 512

11 DATAFILE

12'G:\ APP\ ADMINISTRATOR\ ORADATA\ ORCL\ SYSTEM01.DBF'

13'G:\ APP\ ADMINISTRATOR\ ORADATA\ ORCL\ SYSAUX01.DBF'

14'G:\ APP\ ADMINISTRATOR\ ORADATA\ ORCL\ UNDOTBS01.DBF'

15'G:\ APP\ ADMINISTRATOR\ ORADATA\ ORCL\ USERS01.DBF'

16'G:\ APP\ ADMINISTRATOR\ ORADATA\ ORCL\ EXAMPLE01.DBF'

17 CHARACTER SET ZHS16GBK

CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS NOARCHIVELOG

*

An error occurred on line 1:

ORA-01503: CREATE CONTROLFILE??

ORA-01229: data file 2 is inconsistent with logs

ORA-01110: data file 2:'G:\ APP\ ADMINISTRATOR\ ORADATA\ ORCL\ SYSAUX01.DBF'

We use resetlogs for reconstruction avoidance and redo logs verification

SQL > CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS NOARCHIVELOG

2 MAXLOGFILES 16

3 MAXLOGMEMBERS 3

4 MAXDATAFILES 100

5 MAXINSTANCES 8

6 MAXLOGHISTORY 292

7 LOGFILE

8 GROUP 1'G:\ APP\ ADMINISTRATOR\ ORADATA\ ORCL\ REDO01.LOG' SIZE 50m BLOCKSIZ

E 512

9 GROUP 2'G:\ APP\ ADMINISTRATOR\ ORADATA\ ORCL\ REDO02.LOG' SIZE 50m BLOCKSIZ

E 512

10 GROUP 3'G:\ APP\ ADMINISTRATOR\ ORADATA\ ORCL\ REDO03.LOG' SIZE 50m BLOCKSIZ

E 512

11 DATAFILE

12'G:\ APP\ ADMINISTRATOR\ ORADATA\ ORCL\ SYSTEM01.DBF'

13'G:\ APP\ ADMINISTRATOR\ ORADATA\ ORCL\ SYSAUX01.DBF'

14'G:\ APP\ ADMINISTRATOR\ ORADATA\ ORCL\ UNDOTBS01.DBF'

15'G:\ APP\ ADMINISTRATOR\ ORADATA\ ORCL\ USERS01.DBF'

16'G:\ APP\ ADMINISTRATOR\ ORADATA\ ORCL\ EXAMPLE01.DBF'

17 CHARACTER SET ZHS16GBK

The control file has been created.

SQL > alter database mount

Alter database mount

*

An error occurred on line 1:

ORA-01100:?

SQL > alter session set nls_language='american';-- Chinese character set shows garbled. You need to change the session character set.

Session altered.

SQL > alter database mount

Alter database mount

*

ERROR at line 1:

ORA-01100: database already mounted

SQL > alter database open resetlogs

Alter database open resetlogs

*

ERROR at line 1:

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1:'G:\ APP\ ADMINISTRATOR\ ORADATA\ ORCL\ SYSTEM01.DBF'

To run a recover database, you need to use resetlogs and manually specify archive logs. This is actually one of the redolog.

SQL > recover database

ORA-00283: recovery session canceled due to errors

ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

SQL > recover database using backup controlfile

ORA-00279: change 28088746 generated at 12/16/2017 20:32:45 needed for thread 1

ORA-00289: suggestion:

G:\ APP\ ADMINISTRATOR\ FLASH_RECOVERY_AREA\ ORCL\ ARCHIVELOG\ 2017 December 19\ O1_MF_1_572_%U_.ARC

ORA-00280: change 28088746 for thread 1 is in sequence # 572

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

ORA-00308: cannot open archived log

'G:\ APP\ ADMINISTRATOR\ FLASH_RECOVERY_AREA\ ORCL\ ARCHIVELOG\ 2017 / 12 / 19 / O1 / MFF / 572% Ubun.ARC'

ORA-27041: unable to open file

OSD-04002:?

O/S-Error: (OS 2)?

SQL > archive log list

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 0

Current log sequence 0

SQL > recover database using backup controlfile

ORA-00279: change 28088746 generated at 12/16/2017 20:32:45 needed for thread 1

ORA-00289: suggestion:

G:\ APP\ ADMINISTRATOR\ FLASH_RECOVERY_AREA\ ORCL\ ARCHIVELOG\ 2017 December 19\ O1_MF_1_572_%U_.ARC

ORA-00280: change 28088746 for thread 1 is in sequence # 572

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

G:\ app\ Administrator\ oradata\ orcl\ REDO01.LOG

ORA-00310: archived log contains sequence 571; sequence 572required

ORA-00334: archived log:'G:\ APP\ ADMINISTRATOR\ ORADATA\ ORCL\ REDO01.LOG'

SQL > recover database using backup controlfile

ORA-00279: change 28088746 generated at 12/16/2017 20:32:45 needed for thread 1

ORA-00289: suggestion:

G:\ APP\ ADMINISTRATOR\ FLASH_RECOVERY_AREA\ ORCL\ ARCHIVELOG\ 2017 December 19\ O1_MF_1_572_%U_.ARC

ORA-00280: change 28088746 for thread 1 is in sequence # 572

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

G:\ app\ Administrator\ oradata\ orcl\ REDO02.LOG specified here is one of the redolog

Log applied.

Media recovery complete.

SQL > alter database open

Alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL > alter database open resetlogs

Database altered.

SQL > select file_name from dba_data_files

FILE_NAME

G:\ APP\ ADMINISTRATOR\ ORADATA\ ORCL\ EXAMPLE01.DBF

G:\ APP\ ADMINISTRATOR\ ORADATA\ ORCL\ USERS01.DBF

G:\ APP\ ADMINISTRATOR\ ORADATA\ ORCL\ UNDOTBS01.DBF

G:\ APP\ ADMINISTRATOR\ ORADATA\ ORCL\ SYSAUX01.DBF

G:\ APP\ ADMINISTRATOR\ ORADATA\ ORCL\ SYSTEM01.DBF

SQL > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL > startup

ORACLE instance started.

Total System Global Area 1046224896 bytes

Fixed Size 1379212 bytes

Variable Size 708838516 bytes

Database Buffers 331350016 bytes

Redo Buffers 4657152 bytes

The database is loaded.

The database is already open.

At this point, the recovery is complete.

On the ORACLE server abnormal power outage and control file failure handling steps are shared here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.