oracle 19c数据库制文件删除处理

[数据库专家社区]

1、事前处理：开始归档和闪回数据库
SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=20G;

System altered.

SQL> alter system set DB_RECOVERY_FILE_DEST='/u01/app/oracle/fast_recovery_area';

System altered.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area   3103781608 bytes     

Fixed Size                    8901352 bytes     

Variable Size               637534208 bytes     

Database Buffers           2449473536 bytes     

Redo Buffers                  7872512 bytes     

Database mounted.

SQL> alter database archivelog;

alter database archivelog

SQL> alter database flashback on;

Database altered.

SQL> alter database open;

2、执行备份数据库
[oracle@host01 oradata]$ rman target / catalog rc_admin/RC_ADMIN@host02/RCATPDB

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Mar 17 16:41:36 2024

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CDB5 (DBID=1855293418)

connected to recovery catalog database

RMAN> backup database plus archivelog;

Starting backup at 17-MAR-24

current log archived

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=40 device type=DISK

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=6 RECID=1 STAMP=1163868054

input archived log thread=1 sequence=7 RECID=2 STAMP=1163868071

input archived log thread=1 sequence=8 RECID=3 STAMP=1163868120

channel ORA_DISK_1: starting piece 1 at 17-MAR-24

channel ORA_DISK_1: finished piece 1 at 17-MAR-24

piece handle=/u01/app/oracle/fast_recovery_area/CDB5/backupset/2024_03_17/o1_mf_annnn_TAG20240317T164201_lzfc6st5_.bkp tag=TAG20240317T164201 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 17-MAR-24

Starting backup at 17-MAR-24

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/CDB5/system01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/CDB5/sysaux01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/CDB5/undotbs01.dbf

input datafile file number=00007 name=/u01/app/oracle/oradata/CDB5/users01.dbf

channel ORA_DISK_1: starting piece 1 at 17-MAR-24

channel ORA_DISK_1: finished piece 1 at 17-MAR-24

piece handle=/u01/app/oracle/fast_recovery_area/CDB5/backupset/2024_03_17/o1_mf_nnndf_TAG20240317T164203_lzfc6wnq_.bkp tag=TAG20240317T164203 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07

Finished backup at 17-MAR-24

Starting backup at 17-MAR-24

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=9 RECID=4 STAMP=1163868131

channel ORA_DISK_1: starting piece 1 at 17-MAR-24

channel ORA_DISK_1: finished piece 1 at 17-MAR-24

piece handle=/u01/app/oracle/fast_recovery_area/CDB5/backupset/2024_03_17/o1_mf_annnn_TAG20240317T164213_lzfc755m_.bkp tag=TAG20240317T164213 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 17-MAR-24

Starting Control File and SPFILE Autobackup at 17-MAR-24

piece handle=/u01/app/oracle/fast_recovery_area/CDB5/autobackup/2024_03_17/o1_mf_s_1163868135_lzfc778h_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 17-MAR-24

RMAN> exit

Recovery Manager complete.

Database altered.

3、删除CDB5控制文件
[oracle@host01 CDB5]$ mv control0* /tmp

[oracle@host01 CDB5]$ ls

redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf

[oracle@host01 CDB5]$ sql / as sysdba

SQLcl: Release 19.1 Production on Sun Mar 17 16:44:04 2024

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> shutdown immediate;

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/u01/app/oracle/oradata/CDB5/control01.ctl'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

[oracle@host01 CDB5]$ sql / as sysdba

SQLcl: Release 19.1 Production on Sun Mar 17 16:44:19 2024

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> shutdown abort

ORACLE instance shut down.

SQL>

SQL>

SQL> startup

ORACLE instance started.

ORA-00205: error in identifying control file, check alert log for more info

4、执行恢复
[oracle@host01 CDB5]$

[oracle@host01 CDB5]$ rman target / catalog rc_admin/RC_ADMIN@host02/RCATPDB

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Mar 17 16:44:59 2024

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CDB5 (not mounted)

connected to recovery catalog database

RMAN> list failure;

List of Database Failures

=========================

Failure ID Priority Status    Time Detected Summary

---------- -------- --------- ------------- -------

105        CRITICAL OPEN      17-MAR-24     Control file /u01/app/oracle/oradata/CDB5/control02.ctl is missing

102        CRITICAL OPEN      17-MAR-24     Control file /u01/app/oracle/oradata/CDB5/control01.ctl is missing

RMAN> advise failure;

List of Database Failures

=========================

Failure ID Priority Status    Time Detected Summary

---------- -------- --------- ------------- -------

105        CRITICAL OPEN      17-MAR-24     Control file /u01/app/oracle/oradata/CDB5/control02.ctl is missing

102        CRITICAL OPEN      17-MAR-24     Control file /u01/app/oracle/oradata/CDB5/control01.ctl is missing

analyzing automatic repair options; this may take some time

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=237 device type=DISK

analyzing automatic repair options complete

Mandatory Manual Actions

========================

no manual actions available

Optional Manual Actions

=======================

1. If file /u01/app/oracle/oradata/CDB5/control02.ctl was unintentionally renamed or moved, restore it

2. If file /u01/app/oracle/oradata/CDB5/control01.ctl was unintentionally renamed or moved, restore it

3. If this is a standby database, restore the controlfile for a standby database using RESTORE STANDBY CONTROLFILE FROM AUTOBACKUP command

4. If this is a primary database and a standby database is available, then perform a Data Guard failover initiated from the standby

Automated Repair Options

========================

Option Repair Description

------ ------------------

1      Restore a backup control file 

  Strategy: The repair includes complete media recovery with no data loss

  Repair script: /u01/app/oracle/diag/rdbms/CDB5/CDB5/hm/reco_3950667771.hm

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss

Repair script: /u01/app/oracle/diag/rdbms/CDB5/CDB5/hm/reco_3950667771.hm

contents of repair script:

   # restore control file

   restore controlfile;

   sql 'alter database mount';

Do you really want to execute the above repair (enter YES or NO)? yes

executing repair script

Starting restore at 17-MAR-24

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/CDB5/autobackup/2024_03_17/o1_mf_s_1163868135_lzfc778h_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/CDB5/autobackup/2024_03_17/o1_mf_s_1163868135_lzfc778h_.bkp tag=TAG20240317T164215

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/u01/app/oracle/oradata/CDB5/control01.ctl

output file name=/u01/app/oracle/oradata/CDB5/control02.ctl

Finished restore at 17-MAR-24

sql statement: alter database mount

released channel: ORA_DISK_1

repair failure complete

RMAN> exit

Recovery Manager complete.

[oracle@host01 CDB5]$ ll

total 2453780

-rw-r----- 1 oracle oinstall  10600448 Mar 17 16:47 control01.ctl

-rw-r----- 1 oracle oinstall  10600448 Mar 17 16:47 control02.ctl

-rw-r----- 1 oracle oinstall 209715712 Mar 17 16:43 redo01.log

-rw-r----- 1 oracle oinstall 209715712 Mar 17 16:42 redo02.log

-rw-r----- 1 oracle oinstall 209715712 Mar 17 16:42 redo03.log

-rw-r----- 1 oracle oinstall 555753472 Mar 17 16:42 sysaux01.dbf

-rw-r----- 1 oracle oinstall 943726592 Mar 17 16:42 system01.dbf

-rw-r----- 1 oracle oinstall  33562624 Jul 22  2023 temp01.dbf

-rw-r----- 1 oracle oinstall 356524032 Mar 17 16:42 undotbs01.dbf

-rw-r----- 1 oracle oinstall   5251072 Mar 17 16:42 users01.dbf

[oracle@host01 CDB5]$ sql / as sysdba

SQLcl: Release 19.1 Production on Sun Mar 17 16:47:17 2024

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> select open_mode from v$database;

OPEN_MODE          

--------------------

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: '/u01/app/oracle/oradata/CDB5/system01.dbf'

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

[oracle@host01 CDB5]$

[oracle@host01 CDB5]$

[oracle@host01 CDB5]$ rman target / catalog rc_admin/RC_ADMIN@host02/RCATPDB

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Mar 17 16:48:07 2024

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CDB5 (DBID=1855293418, not open)

connected to recovery catalog database

RMAN> list failure;

Database Role: PRIMARY

List of Database Failures

=========================

Failure ID Priority Status    Time Detected Summary

---------- -------- --------- ------------- -------

156        CRITICAL OPEN      17-MAR-24     System datafile 1: '/u01/app/oracle/oradata/CDB5/system01.dbf' needs media recovery

153        CRITICAL OPEN      17-MAR-24     Control file needs media recovery

159        HIGH     OPEN      17-MAR-24     One or more non-system datafiles need media recovery

RMAN> advise failure;

Starting implicit crosscheck backup at 17-MAR-24

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=259 device type=DISK

Crosschecked 3 objects

Finished implicit crosscheck backup at 17-MAR-24

Starting implicit crosscheck copy at 17-MAR-24

using channel ORA_DISK_1

Finished implicit crosscheck copy at 17-MAR-24

searching for all files in the recovery area

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: /u01/app/oracle/fast_recovery_area/CDB5/autobackup/2024_03_17/o1_mf_s_1163868135_lzfc778h_.bkp

Database Role: PRIMARY

List of Database Failures

=========================

Failure ID Priority Status    Time Detected Summary

---------- -------- --------- ------------- -------

156        CRITICAL OPEN      17-MAR-24     System datafile 1: '/u01/app/oracle/oradata/CDB5/system01.dbf' needs media recovery

153        CRITICAL OPEN      17-MAR-24     Control file needs media recovery

159        HIGH     OPEN      17-MAR-24     One or more non-system datafiles need media recovery

analyzing automatic repair options; this may take some time

using channel ORA_DISK_1

analyzing automatic repair options complete

Mandatory Manual Actions

========================

no manual actions available

Optional Manual Actions

=======================

1. If you have the correct version of the control file, then shutdown the database and replace the old control file

2. If you restored the wrong version of data file /u01/app/oracle/oradata/CDB5/system01.dbf, then replace it with the correct one

3. If you restored the wrong version of data file /u01/app/oracle/oradata/CDB5/sysaux01.dbf, then replace it with the correct one

4. If you restored the wrong version of data file /u01/app/oracle/oradata/CDB5/undotbs01.dbf, then replace it with the correct one

5. If you restored the wrong version of data file /u01/app/oracle/oradata/CDB5/users01.dbf, then replace it with the correct one

Automated Repair Options

========================

Option Repair Description

------ ------------------

1      Recover database 

  Strategy: The repair includes complete media recovery with no data loss

  Repair script: /u01/app/oracle/diag/rdbms/CDB5/CDB5/hm/reco_1516513613.hm

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss

Repair script: /u01/app/oracle/diag/rdbms/CDB5/CDB5/hm/reco_1516513613.hm

contents of repair script:

   # recover database

   recover database;

   alter database open resetlogs;

Do you really want to execute the above repair (enter YES or NO)? yes

executing repair script

Starting recover at 17-MAR-24

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 10 is already on disk as file /u01/app/oracle/oradata/CDB5/redo01.log

archived log file name=/u01/app/oracle/oradata/CDB5/redo01.log thread=1 sequence=10

media recovery complete, elapsed time: 00:00:00

Finished recover at 17-MAR-24

Statement processed

new incarnation of database registered in recovery catalog

starting full resync of recovery catalog

full resync complete

repair failure complete

RMAN> exit

5、查看修复好后的状态
Recovery Manager complete.

[oracle@host01 CDB5]$ sql / as sysdba

SQLcl: Release 19.1 Production on Sun Mar 17 16:49:12 2024

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> select open_mode from v$database;

OPEN_MODE          

--------------------

READ WRITE         

SQL>