“This is the 14th day of my participation in the Gwen Challenge in November. Check out the details: The Last Gwen Challenge in 2021.”

View the path and content of the control file

SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /oradata/orcl/control01.ctl, /
                                                 u01/app/oracle/fast_recovery_a
                                                 rea/orcl/control02.ctl

[oracle@orcl:/oradata/orcl]$ ll /oradata/orcl/control01.ctl 
-rw-r----- 1 oracle oinstall 9748480 Apr 15 10:01 /oradata/orcl/control01.ctl

[oracle@orcl:/oradata/orcl]$ ll /u01/app/oracle/fast_recovery_area/orcl/control02.ctl
-rw-r----- 1 oracle oinstall 9748480 Apr 15 10:03 /u01/app/oracle/fast_recovery_area/orcl/control02.ctl

-- Make sure archiving is enabled
SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /archivelog
Oldest online log sequence     10
Current log sequence           12

SQL> select distinct dbms_rowid.rowid_block_number(rowid) from props$;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
                                 801

-- Control file contents
Generate control files
SQL> alter database backup controlfile to trace as '/home/oracle/ctlbak.ctl';

Database altered.

-- Check the trace content
[oracle@orcl:/home/oracle]$ cat ctlbak.ctl 
-- The following are current System-scope REDO Log Archival related
-- parameters and can be included in the database initialization file.
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
--
-- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
--
-- DB_UNIQUE_NAME="orcl"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
-- LOG_ARCHIVE_MAX_PROCESSES=4
-- STANDBY_FILE_MANAGEMENT=MANUAL
-- STANDBY_ARCHIVE_DEST=? /dbs/arch
-- FAL_CLIENT=''
-- FAL_SERVER=''
--
-- LOG_ARCHIVE_DEST_1='LOCATION=/archivelog'
-- LOG_ARCHIVE_DEST_1='OPTIONAL REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_1='REGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_1=ENABLE

--
-- Below are two sets of SQL statements, each of which creates a new
-- control file and uses it to open the database. The first set opens
-- the database with the NORESETLOGS option and should be used only if
-- the current versions of all online logs are available. The second
-- set opens the database with the RESETLOGS option and should be used
-- if online logs are unavailable.
-- The appropriate set of statements can be copied from the trace into
-- a script file, edited as necessary, and executed when there is a
-- need to re-create the control file.
--
-- Set #1. NORESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.

-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/oradata/orcl/redo01.log'  SIZE 120M BLOCKSIZE 512.GROUP 2 '/oradata/orcl/redo02.log'  SIZE 120M BLOCKSIZE 512.GROUP 3 '/oradata/orcl/redo03.log'  SIZE 120M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/oradata/orcl/system01.dbf'.'/oradata/orcl/sysaux01.dbf'.'/oradata/orcl/undotbs01.dbf'.'/oradata/orcl/users01.dbf'.'/oradata/orcl/example01.dbf'
CHARACTER SET AL32UTF8
;

-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/archivelog/1_1_1069941729.dbf';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE

-- Database can now be opened normally.
ALTER DATABASE OPEN;

-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/orcl/temp01.dbf'
     SIZE 30408704  REUSE AUTOEXTEND OFF;
-- End of tempfile additions.
--
-- Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.

-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/oradata/orcl/redo01.log'  SIZE 120M BLOCKSIZE 512.GROUP 2 '/oradata/orcl/redo02.log'  SIZE 120M BLOCKSIZE 512.GROUP 3 '/oradata/orcl/redo03.log'  SIZE 120M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/oradata/orcl/system01.dbf'.'/oradata/orcl/sysaux01.dbf'.'/oradata/orcl/undotbs01.dbf'.'/oradata/orcl/users01.dbf'.'/oradata/orcl/example01.dbf'
CHARACTER SET AL32UTF8
;

-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/archivelog/1_1_1069941729.dbf';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE

-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;

-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/orcl/temp01.dbf'
     SIZE 30408704  REUSE AUTOEXTEND OFF;
-- End of tempfile additions.
--
Copy the code

The current database has two control files, one in the data file path and the other in the fast flashback area.

Ii. Loss of simulation control files:

1. All control files are not lost

Delete control files in the blink area to forcibly shut down the database.

[oracle@orcl:/oradata/orcl]$ rm -rf /u01/app/oracle/fast_recovery_area/orcl/control02.ctl 
[oracle@orcl:/oradata/orcl]$ ll /u01/app/oracle/fast_recovery_area/orcl/control02.ctl     
ls: cannot access /u01/app/oracle/fast_recovery_area/orcl/control02.ctl: No such file or directory

-- Open a new session, and the database is in the error state, no matter what operation will be reported, and then forcibly shut down the database
SQL> select open_mode from v$database;
select open_mode from v$database
                      *
ERROR at line 1:
ORA- 00210.: cannot open the specified control file
ORA- 00202.: control file:
'/u01/app/oracle/fast_recovery_area/orcl/control02.ctl'
ORA- 27041.: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SQL> shutdown abort
ORACLE instance shut down.
SQL> 
Copy the code

Copy a control file from the data file path to the flash zone and try to close it again.

Copy control files
[oracle@orcl:/u01/app/oracle/fast_recovery_area/orcl]$ cp /oradata/orcl/control01.ctl control02.ctl
[oracle@orcl:/u01/app/oracle/fast_recovery_area/orcl]$ ls
control02.ctl

-- Open database
SQL> startup
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size                  2253664 bytes
Variable Size             452988064 bytes
Database Buffers         1140850688 bytes
Redo Buffers                7319552 bytes
Database mounted.
Database opened.
Copy the code

2, all lost, control file backup

There are two ways:

1. Rebuild the control file using the backup file

2, through rman restore control file, need to open the database through resetlogs mode

1. Rebuild control files through backup files

CREATE CONTROLFILE REUSE DATABASE CREATE CONTROLFILE REUSE DATABASE"ORCL" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/oradata/orcl/redo01.log'  SIZE 120M BLOCKSIZE 512,
  GROUP 2 '/oradata/orcl/redo02.log'  SIZE 120M BLOCKSIZE 512,
  GROUP 3 '/oradata/orcl/redo03.log'  SIZE 120M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/oradata/orcl/system01.dbf'.'/oradata/orcl/sysaux01.dbf'.'/oradata/orcl/undotbs01.dbf'.'/oradata/orcl/users01.dbf'.'/oradata/orcl/example01.dbf'CHARACTER SET AL32UTF8; SQL> startup nomount ORACLE instance started. Total System Global Area 1603411968 bytes Fixed Size 2253664 bytes Variable Size 452988064 bytes Database Buffers 1140850688 bytes Redo Buffers 7319552 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 '/oradata/orcl/redo01.log'  SIZE 120M BLOCKSIZE 512,
  9    GROUP 2 '/oradata/orcl/redo02.log'  SIZE 120M BLOCKSIZE 512,
 10    GROUP 3 '/oradata/orcl/redo03.log'  SIZE 120M BLOCKSIZE 512
 11  -- STANDBY LOGFILE
 12  DATAFILE
 13    '/oradata/orcl/system01.dbf',
 14    '/oradata/orcl/sysaux01.dbf',
 15    '/oradata/orcl/undotbs01.dbf',
 16    '/oradata/orcl/users01.dbf',
 17    '/oradata/orcl/example01.dbf'
 18  CHARACTER SET AL32UTF8;

Control file created.

SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/orcl/temp01.dbf';

Tablespace altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE
Copy the code

The control file is successfully restored. Procedure

Restore control files through rMAN

Rman backup control file

RMAN> backup current controlfile format '/home/oracle/ctrl.ora';

Starting backup at 15-APR-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=29 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup setchannel ORA_DISK_1: starting piece 1 at 15-APR-21 channel ORA_DISK_1: finished piece 1 at 15-APR-21 piece handle=/home/oracle/ctrl.ora tag=TAG20210415T104457 comment=NONE channel ORA_DISK_1:  backupsetcomplete, elapsed time: 00:00:01 Finished backup at 15-APR-21 RMAN> list backup of controlfile; List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 1 Full 9.64 M DISK 00:00:01 15 - APR - 21 BP Key: 1 the Status: AVAILABLE Compressed: NO Tag: TAG20210415T104457 Piece Name: /home/oracle/ctrl.ora Control File Included: Ckp SCN: 1219869 Ckp time: 15-APR-21Copy the code

Rm deleted all control files, tried to add data files, forcibly shut down the database, and tried to enable the database to mount state.

[oracle@orcl:/u01/app/oracle/fast_recovery_area/orcl]$ rm control02.ctl [oracle@orcl:/u01/app/oracle/fast_recovery_area/orcl]$ rm /oradata/orcl/control01.ctl [oracle@orcl:/u01/app/oracle/fast_recovery_area/orcl]$ ll total 0 [oracle@orcl:/u01/app/oracle/fast_recovery_area/orcl]$  ll /oradata/orcl/control01.ctl ls: cannot access /oradata/orcl/control01.ctl: No such file or directory SQL> alter tablespace USERS add datafile; alter tablespace USERS add datafile * ERROR at line 1: ORA-00603: ORACLE server session terminated by fatal error ORA-00210: cannot open the specified control file ORA-00202: control file:'/oradata/orcl/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/oradata/orcl/control01.ctl'ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3 Process ID: 2333 Session ID: 1 Serial number: 9 SQL> shutdown abort; ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 1603411968 bytes Fixed Size 2253664 bytes Variable Size 452988064 bytes Database Buffers 1140850688 bytes Redo Buffers 7319552 bytes ORA-00205:  errorin identifying control file, check alert log for more info
Copy the code

Enable the database to nomount state, rman restore control files, restore the database

SQL> startup nomount ORACLE instance started. Total System Global Area 1603411968 bytes Fixed Size 2253664 bytes Variable Size 452988064 bytes Database Buffers 1140850688 bytes Redo Buffers 7319552 bytes --rman restore control file RMAN> restore controlfile from'/home/oracle/ctrl.ora';

Starting restore at 15-APR-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/oradata/orcl/control01.ctl output file Name =/ U01 /app/oracle/fast_recovery_area/ orCL /control02. CTL Finished restore at 15-APR-21 -- Start the database to mount state SQL> ALTER database mount; Database altered. RMAN> recover Database; Starting recover at 15-APR-21 released channel: ORA_DISK_1 Starting implicit crosscheck backup at 15-APR-21 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=20 devicetype=DISK
Finished implicit crosscheck backup at 15-APR-21

Starting implicit crosscheck copy at 15-APR-21
using channel ORA_DISK_1
Finished implicit crosscheck copy at 15-APR-21

searching for all files in the recovery area
cataloging files...
no files cataloged

using channel ORA_DISK_1
RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
ORACLE error from target database: 
ORA-19922: there is no parent row with id 0 and level 1


starting media recovery

archived log for thread 1 with sequence 12 is already on disk as file /oradata/orcl/redo03.log
archived log file name=/oradata/orcl/redo03.log thread=1 sequence=12
media recovery complete, elapsed time: 00:00:00
Finished recover at 15-APR-21
Copy the code

Open the database through resetLogs

SQL> alter database open resetlogs;

Database altered.
Copy the code

3, all lost, no backup

Delete all control files and no backup control files

SQL> select open_mode from v$database;
select open_mode from v$database
                      *
ERROR at line 1:
ORA- 00210.: cannot open the specified control file
ORA- 00202.: control file: '/oradata/orcl/control01.ctl'
ORA- 27041.: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SQL> shutdown abort
ORACLE instance shut down.
Copy the code

Manually create control files

Db_name [oracle@orcl:/home/oracle]$grep - obtain information from spfile or pfile"db_name" pfile.ora 
*.db_name='orcl'--2. Character set (via DDifView system01 data file) ddif=system01.dbf of=lucifer bs=8192 skip=801 --3. Obtain the data file and log file names [oracle@orcl:/oradata/ orCL]$ll total 2083264 -rw-r-----. 1 Oracle oinstall 328343552 Apr 15 15:14 example01.dbf -rw-r-----. 1 oracle oinstall 125829632 Apr 15 15:14 redo01.log -rw-r-----. 1 oracle oinstall 125829632 Apr 15 15:14 redo02.log -rw-r-----. 1 oracle oinstall 125829632 Apr 15 15:17 redo03.log -rw-r-----. 1 oracle oinstall 545267712 Apr 15 15:14 sysaux01.dbf -rw-r-----. 1 oracle oinstall 786440192 Apr 15 15:14 system01.dbf -rw-r-----. 1 oracle oinstall 30416896 Apr 15 15:14 temp01.dbf -rw-r-----. 1 oracle oinstall 89137152 Apr 15 15:14 undotbs01.dbf -rw-r-----. 1 oracle oinstall 5251072 Apr 15 15:14 users01. DBF Reuse) -- open database to nomount STARTUP nomount; -- CREATE CONTROLFILE CREATE CONTROLFILE REUSE DATABASE"ORCL" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/oradata/orcl/redo01.log'  SIZE 120M BLOCKSIZE 512,
  GROUP 2 '/oradata/orcl/redo02.log'  SIZE 120M BLOCKSIZE 512,
  GROUP 3 '/oradata/orcl/redo03.log'  SIZE 120M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/oradata/orcl/system01.dbf'.'/oradata/orcl/sysaux01.dbf'.'/oradata/orcl/undotbs01.dbf'.'/oradata/orcl/users01.dbf'.'/oradata/orcl/example01.dbf'CHARACTER SET AL32UTF8 ; RECOVER DATABASE; 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 '/oradata/orcl/redo01.log'  SIZE 120M BLOCKSIZE 512,
  9    GROUP 2 '/oradata/orcl/redo02.log'  SIZE 120M BLOCKSIZE 512,
 10    GROUP 3 '/oradata/orcl/redo03.log'  SIZE 120M BLOCKSIZE 512
 11  -- STANDBY LOGFILE
 12  DATAFILE
 13    '/oradata/orcl/system01.dbf',
 14    '/oradata/orcl/sysaux01.dbf',
 15    '/oradata/orcl/undotbs01.dbf',
 16    '/oradata/orcl/users01.dbf',
 17    '/oradata/orcl/example01.dbf'
 18  CHARACTER SET AL32UTF8
 19  ;

Control file created.

SQL> RECOVER DATABASE;
Media recovery complete.
Copy the code

The database is opened and the control file is successfully restored

SQL> ALTER DATABASE OPEN;

Database altered.

This is where temporary files need to be reused
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/orcl/temp01.dbf' REUSE;

Tablespace altered.
Copy the code

Three, advice,

1. Create multiple control files and store them in multiple folders of multiple storage devices to avoid file deletion or storage damage.

2. Open archive mode.

3, save the current library control file generation script (including important information: DBname, character set, file path).

4. Back up database files periodically to prevent loss. You are advised to save the backup files on the source end.