Shulou(Shulou.com)06/01 Report--

Uploading pictures here has failed all the time. If you want to view details and screenshots, you can download the attachment.

First, take a look at the understanding of the control file:

The control file is a binary file that records the physical structure of the database. A control file belongs to only one database. When you create a database, create a control file. When the physical structure of the database changes, Oracle updates the control file and cannot modify the contents manually.

The contents of the control file include: database name, time stamp created by the database, name and location of the data file, name and location of the redo log (online redo log file), tablespace information, serial number of the current log, checkpoint information, the latest RMAN backup information, archive log information

When this information changes or changes, the oracle database automatically updates the latest information to the controlfile file.

When starting the database, Oracle obtains the name and location of the control file from the initialization parameter file (pfile/spfile), opens the control file, then reads the information of the data file and online log file from the control file, and finally opens the database. When the database is running, Oracle modifies the control file, so once the control file is corrupted, the database will not function properly.

There are several prerequisites and differences for creating a control file:

Premise:

1. If it is under RAC, you need to shut down the cluster:

Alter system set cluster_database=FALSE scope=spfile sid='*'

Otherwise, an error will be reported when you recover.

two。 The database can be rebuilt only when it is in mount or OPEN state.

Difference:

There are two ways to restore control files: 1.resetlogs recovery and noresetlogs recovery.

Let's start with the recovery steps:

Rebuild control file

Backup mode

Sql > alter database backup controlfile to trace as'/ u01Actioncontrolfile.trcd'

Create controlfile backup files locally

Alter database backup controlfile to'+ UNDO/ORCL/control'

This is the backup control file to the ASM management storage.

If you don't know where the current control file is

View location: select * from v$controlfile

First, the library can enter the mount state or the OPEN state:

SQL > startup mount; or direct open status

SQL > alter database backup controlfile to trace as'/ u01According controlfile.trc'

Enter / u01 / below

Then edit the control file

It contains two parts of the recovery script

One is to use norestlogs and the other is a script to restore using resetlogs:

The contents will not be listed in detail:

Those that identify Set # 1.NORESETLOGS case are restored using noresetlogs

Those that identify Set # 1.RESETLOGS case are restored using resetlogs

The contents are as follows:

The first is the script for noresetlogs recovery

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG

MAXLOGFILES 192

MAXLOGMEMBERS 3

MAXDATAFILES 1024

MAXINSTANCES 32

MAXLOGHISTORY 292

LOGFILE

GROUP 1.257.912261307' SIZE 50m BLOCKSIZE 512

GROUP 2.258.912261307' SIZE 50m BLOCKSIZE 512

GROUP 3.259.912261453 'SIZE 50m BLOCKSIZE 512

GROUP 4.260.912261453 'SIZE 50m BLOCKSIZE

DATAFILE

'+ DATA/orcl/datafile/system.256.912261237'

'+ DATA/orcl/datafile/sysaux.257.912261239'

'+ DATA/orcl/datafile/undotbs1.258.912261239'

'+ DATA/orcl/datafile/users.259.912261239'

'+ DATA/orcl/datafile/undotbs2.261.912261395'

CHARACTER SET UTF8

VARIABLE RECNO NUMBER

EXECUTE: RECNO: = SYS.DBMS_BACKUP_RESTORE.SETCONFIG ('CONTROLFILE AUTOBACKUP','ON')

RECOVER DATABASE

ALTER SYSTEM ARCHIVE LOG ALL

ALTER DATABASE OPEN

ALTER TABLESPACE TEMP ADD TEMPFILE'+DATA/orcl/tempfile/temp.260.912261313'

SIZE 33554432 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M

Here is a script for resetlog recovery:

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS ARCHIVELOG

MAXLOGFILES 192

MAXLOGMEMBERS 3

MAXDATAFILES 1024

MAXINSTANCES 32

MAXLOGHISTORY 292

LOGFILE

GROUP 1.257.912261307' SIZE 50m BLOCKSIZE 512

GROUP 2.258.912261307' SIZE 50m BLOCKSIZE 512

DATAFILE

'+ DATA/orcl/datafile/system.256.912261237'

'+ DATA/orcl/datafile/sysaux.257.912261239'

'+ DATA/orcl/datafile/undotbs1.258.912261239'

'+ DATA/orcl/datafile/users.259.912261239'

'+ DATA/orcl/datafile/undotbs2.261.912261395'

CHARACTER SET UTF8

VARIABLE RECNO NUMBER

EXECUTE: RECNO: = SYS.DBMS_BACKUP_RESTORE.SETCONFIG ('CONTROLFILE AUTOBACKUP','ON')

RECOVER DATABASE USING BACKUP CONTROLFILE

ALTER DATABASE ADD LOGFILE THREAD 2

GROUP 3.259.912261453 'SIZE 50m BLOCKSIZE 512 REUSE

GROUP 4.260.912261453 'SIZE 50m BLOCKSIZE 512 REUSE

ALTER DATABASE OPEN RESETLOGS

ALTER TABLESPACE TEMP ADD TEMPFILE'+DATA/orcl/tempfile/temp.260.912261313'

SIZE 33554432 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M

All right, let's stop the library and restore it:

Boot to nomount:

Startup nomount

Copy and paste the following script directly in nomount:

CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG

MAXLOGFILES 192

MAXLOGMEMBERS 3

MAXDATAFILES 1024

MAXINSTANCES 32

MAXLOGHISTORY 292

LOGFILE

GROUP 1.257.912261307' SIZE 50m BLOCKSIZE 512

GROUP 2.258.912261307' SIZE 50m BLOCKSIZE 512

GROUP 3.259.912261453 'SIZE 50m BLOCKSIZE 512

GROUP 4'+ UNDO/orcl/onlinelog/group_4.260.912261453' SIZE 50m BLOCKSIZE 512

DATAFILE

'+ DATA/orcl/datafile/system.256.912261237'

'+ DATA/orcl/datafile/sysaux.257.912261239'

'+ DATA/orcl/datafile/undotbs1.258.912261239'

'+ DATA/orcl/datafile/users.259.912261239'

'+ DATA/orcl/datafile/undotbs2.261.912261395'

CHARACTER SET UTF8

Okay

Now the control file is created; if an error is reported, check to see if the cluster is not shut down in RAC

Check the current status: it has reached the MOUNTED state.

Perform the following:

VARIABLE RECNO NUMBER

EXECUTE: RECNO: = SYS.DBMS_BACKUP_RESTORE.SETCONFIG ('CONTROLFILE AUTOBACKUP','YES')

In the back.

RECOVERY DATABASE does not need to be executed because there are already data files.

It's just a control file. No.

Direct ALTER DATABASE OPEN

Successfully started the database

Finally, execute the following TEM tablespace

ALTER TABLESPACE TEMP ADD TEMPFILE'+DATA/orcl/tempfile/temp.260.912261313'

SIZE33554432 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M

Reconstruction of controlfile by resetlogs

The data file is in, but the log file is not available in this way.

Boot to nomount:

Startup nomount

Copy and paste the following script directly in nomount:

CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS ARCHIVELOG

MAXLOGFILES 192

MAXLOGMEMBERS 3

MAXDATAFILES 1024

MAXINSTANCES 32

MAXLOGHISTORY 292

LOGFILE

GROUP 1.257.912261307' SIZE 50m BLOCKSIZE 512

GROUP 2.258.912261307' SIZE 50m BLOCKSIZE 512

DATAFILE

'+ DATA/orcl/datafile/system.256.912261237'

'+ DATA/orcl/datafile/sysaux.257.912261239'

'+ DATA/orcl/datafile/undotbs1.258.912261239'

'+ DATA/orcl/datafile/users.259.912261239'

'+ DATA/orcl/datafile/undotbs2.261.912261395'

CHARACTER SET UTF8

Okay

Check the current status: it has reached the MOUNTED state.

Perform the following:

VARIABLE RECNO NUMBER

EXECUTE: RECNO: = SYS.DBMS_BACKUP_RESTORE.SETCONFIG ('CONTROLFILE AUTOBACKUP','YES')

Here, you also need to resetlogs when you start the database because it is restored with resetlogs.

ALTER DATABASE OPEN resetlogs

Successfully started the database

Finally, execute the following TEM tablespace

ALTER TABLESPACE TEMP ADD TEMPFILE'+DATA/orcl/tempfile/temp.260.912261313'

SIZE33554432 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M

Ok, this has also been restored successfully.

Summary:

The two recovery methods are basically the same, only when the database is started, it can be operated according to the situation of the database at that time.

However, in general, it is not possible to lose the control file. And at least two copies of the control files should be backed up and placed in different locations.

If there are any deficiencies or omissions, please discuss them together and make progress together.

Attachment: http://down.51cto.com/data/2367810

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.