Backup, recovery, and multiplexing of Oracle Control Files

Last Update:2014-06-14 Source: Internet

Author: User

Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞

Preface

Today we will share with you the technology on Oracle control files. Without a long journey or even a thousand miles, without a small flow, it cannot become a sea of rivers. Oracle technology is profound and profound. With the power of my own person, it is no different from the world. Here, I hope that readers can interact well with me and make progress together. Today's discussion will begin immediately after the book is published.

Concept of Oracle backup recovery

1. Brief description of the Control File
Since today's topic is a control file, let's take a brief look at the knowledge points of the control file. By default, control files are generally located in the same directory as data files and log files (of course, it is no problem not to put them here ...... See your personal preferences). The specific SQL statement is as follows:
SYS @ ENMOEDU> select name from v $ controlfile;

NAME
------------------------------
/U01/app/oracle/oradata/ENMOEDU/control01.ctl
/U01/app/oracle/fast_recovery_area/ENMOEDU/control02.ctl
The path and name of my control file are clearly displayed here. Also, careful readers should find out that I am a very careful person, my control files are in two copies. In this case, if one of my control files is accidentally deleted or damaged, I have another control file to ensure the normal operation and startup of my database (I accidentally introduced the multiplexing technology in advance ...... More detailed configuration and resolution will be provided later ).
I want to go into details about the role of control files? As many as 90 years old and as five years old, the importance of controlling documents can be as rare as possible. The control file contains the location of the database and online redo logs, and many important information. If the control file is lost or damaged, the database cannot be started and run normally. Since the control file is so important, how should we back up and restore it?

Ii. control file backup and recovery
(1) User-managed backup and recovery

First, go to the Oracle trace directory.
[Oracle @ ENMOEDU trace] $ cd/u01/app/oracle/diag/rdbms/enmoedu/ENMOEDU/trace

Run the tail command to open the alert_ENMOEDU.log file.
[Oracle @ ENMOEDU trace] $ tail-100f alert_ENMOEDU.log

Open another window and run the following command:
SYS @ ENMOEDU> alter database backup controlfile to trace;
Database altered.

We can see the following information in alert. ENMOEDU. log:
Mon Mar 10 15:22:20 2014
Alter database backup controlfile to trace
Backup controlfile written to trace file/u01/app/oracle/diag/rdbms/enmoedu/ENMOEDU/trace/ENMOEDU_ora_18205.trc
Completed: alter database backup controlfile to trace

Based on the above information, we can view the prompt File
[Oracle @ ENMOEDU ~] $ Cat/u01/app/oracle/diag/rdbms/enmoedu/ENMOEDU/trace/ENMOEDU_ora_18205.trc

In the vast amount of information, we can find the command to create a control file
Create controlfile reuse database "ENMOEDU" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
# MAXLOGHISTORY 292
LOGFILE
GROUP 1'/u01/app/oracle/oradata/ENMOEDU/redo01.log 'SIZE 50 m blocksize 512,
GROUP 2 '/u01/app/oracle/oradata/ENMOEDU/redo02.log' SIZE 50 m blocksize 512,
GROUP 3 '/u01/app/oracle/oradata/ENMOEDU/redo03.log' SIZE 50 m blocksize 512
-- STANDBY LOGFILE
DATAFILE
'/U01/app/oracle/oradata/ENMOEDU/system01.dbf ',
'/U01/app/oracle/oradata/ENMOEDU/sysaux01.dbf ',
'/U01/app/oracle/oradata/ENMOEDU/undotbs01.dbf ',
'/U01/app/oracle/oradata/ENMOEDU/users01.dbf ',
'/U01/app/oracle/oradata/ENMOEDU/example01.dbf ',
'/U01/app/oracle/oradata/ENMOEDU/test01.dbf ',
'/U01/app/oracle/oradata/ENMOEDU/test02.dbf'
Character set AL32UTF8;

Later, we can use these commands to create a control file. The following is a simulated experiment environment that controls file loss and damage:
[Root @ ENMOEDU ~] # Rm-rf/u01/app/oracle/oradata/ENMOEDU/control01.ctl
[Root @ ENMOEDU ~] # Rm-rf/u01/app/oracle/fast_recovery_area/ENMOEDU/control02.ctl

I deleted both the control files, which is thorough ~ Then try to start the database.
[Oracle @ ENMOEDU ~] $ Sqlplus/as sysdba
SQL * Plus: Release 11.2.0.3.0 Production on Mon Mar 10 15:47:54 2014
Copyright (c) 1982,201 1, Oracle. All rights reserved.
Connected to an idle instance.
SYS @ ENMOEDU> startup
ORACLE instance started.

Total System Global Area 422670336 bytes
Fixed Size 1345380 bytes
Variable Size 352323740 bytes
Database Buffers 62914560 bytes
Redo Buffers 6086656 bytes
ORA-00205: error in identifying control file, check alert log for more info

We can see that the database cannot be started normally. Then we can start the database to the nomount state for restoration.
SYS @ ENMOEDU> shutdown abort
ORACLE instance shut down.
SYS @ ENMOEDU> startup nomount
ORACLE instance started.

Total System Global Area 422670336 bytes
Fixed Size 1345380 bytes
Variable Size 352323740 bytes
Database Buffers 62914560 bytes
Redo Buffers 6086656 bytes
SYS @ ENMOEDU> create controlfile reuse database "ENMOEDU" RESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7. LOGFILE
8 GROUP 1'/u01/app/oracle/oradata/ENMOEDU/redo01.log 'SIZE 50 m blocksize 512,
9 GROUP 2 '/u01/app/oracle/oradata/ENMOEDU/redo02.log' SIZE 50 m blocksize 512,
10 GROUP 3'/u01/app/oracle/oradata/ENMOEDU/redo03.log 'size 50 m blocksize 512
11 -- STANDBY LOGFILE
12 DATAFILE
13'/u01/app/oracle/oradata/ENMOEDU/system01.dbf ',
14'/u01/app/oracle/oradata/ENMOEDU/sysaux01.dbf ',
15'/u01/app/oracle/oradata/ENMOEDU/undotbs01.dbf ',
16'/u01/app/oracle/oradata/ENMOEDU/users01.dbf ',
17 '/u01/app/oracle/oradata/ENMOEDU/example01.dbf ',
18'/u01/app/oracle/oradata/ENMOEDU/test01.dbf ',
19 '/u01/app/oracle/oradata/ENMOEDU/test02.dbf'
20 character set AL32UTF8;

Control file created.

Try to enable database
SYS @ ENMOEDU> alter database open
2;
Alter database open
*
ERROR at line 1:
The ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

Error message. Follow the prompts to enable the database:
SYS @ ENMOEDU> alter database open resetlogs;

Database altered.

The database has started properly.

The above situation is a very optimistic and easily recoverable simulated experiment site, but in actual situations, the environment is much worse. When the control file is often lost, the user will perform a lot of operations, such as creating a tablespace, which makes it much more difficult to restore the control file. The road is long, and I will go up and down, and my desire for technology will never end. Next I will introduce how to restore the control file when the backup control file is too long.
First, back up our control file. Because I used multiplexing, should I back up both control files? According to international practice, when the author sends a question like this to the reader, the answer is often no. However, as a technical engineer, I am not required to back up two copies. Because the two control files are identical, you only need to back up one copy. Remember to pay attention to the file name during recovery.

Back up the control file. In this article, back up the control file to the Home Directory of Oracle to give the control file a home warmth.
[Oracle @ ENMOEDU] $ cp control01.ctl ~

Create a tablespace and a file to make the current control file and backup file different.
SYS @ ENMOEDU> create tablespace my datafile '/u01/app/oracle/oradata/ENMOEDU/I 01. dbf' size 100 m;

Tablespace created.

Check the status of the current online log and switch the log to generate an archive.
SYS @ ENMOEDU> select group #, status from v $ log;

GROUP # STATUS
--------------------------
1 CURRENT
2 INACTIVE
3 INACTIVE

SYS @ ENMOEDU> alter system archive log current;

System altered.