Rman Restore database khi mất Controlfiles Oracle Database 19c

Đăng vào Tác giả Datalinks.vn 0

Bài viết này sẽ hướng dẫn bạn cách khôi phục lại CSDL khi bị mất Controlfiles.

Các công việc thực hiện:

  • Kiểm tra Controlfiles hiện tại
  • Xoá controlfiles trong OS level
  • Thêm mới tablespace (sẽ bị lỗi do không có controlfile vì nó lưu đường dẫn của datafile)
  • Shutdown instance và starup nomount
  • Restore controlfile
  • Mount và recover database, open resetlogs

oracle rman plugin Archives - SysDBASoft IT

Kiểm tra controlfiles hiện tại:

select name from v$controlfile;

NAME

——————————————————————————–

/u01/app/oracle/oradata/DUODB/controlfile/o1_mf_m3bvy4o4_.ctl

/u01/app/oracle/fast_recovery_area/DUODB/controlfile/o1_mf_m3bvy4p9_.ctl

Tạo bản backup:

exit

rman target /

show all;

=> đảm bảo rằng Controlfiles autobackup đang ON

backup database plus archivelog;

RMAN> show all;

using target database control file instead of recovery catalog

RMAN configuration parameters for database with db_unique_name DUODB are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP ON; # default

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘%F’; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 10 BACKUP TYPE TO BACKUPSET;

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM ‘AES128’; # default

CONFIGURE COMPRESSION ALGORITHM ‘BASIC’ AS OF RELEASE ‘DEFAULT’ OPTIMIZE FOR LOAD TRUE ; # default

CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/u01/app/oracle/product/19.3.0/dbhome_1/dbs/snapcf_duodb.f’; # default

using channel ORA_DISK_1

using channel ORA_DISK_2

using channel ORA_DISK_3

using channel ORA_DISK_4

using channel ORA_DISK_5

using channel ORA_DISK_6

using channel ORA_DISK_7

using channel ORA_DISK_8

using channel ORA_DISK_9

using channel ORA_DISK_10

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=39 STAMP=1168645424

channel ORA_DISK_1: starting piece 1 at 10-MAY-24

channel ORA_DISK_1: finished piece 1 at 10-MAY-24

piece handle=/u01/app/oracle/fast_recovery_area/DUODB/backupset/2024_05_10/o1_mf_annnn_TAG20240510T234344_m3wmp0pq_.bkp tag=TAG20240510T234344 comment=NONE

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

Finished backup at 10-MAY-24

Starting Control File and SPFILE Autobackup at 10-MAY-24

piece handle=/u01/app/oracle/fast_recovery_area/DUODB/autobackup/2024_05_10/o1_mf_s_1168645425_m3wmp20h_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 10-MAY-24

Xoá controlfile trong OS:

rm /u01/app/oracle/oradata/DUODB/controlfile/o1_mf_m3bvy4o4_.ctl

rm /u01/app/oracle/fast_recovery_area/DUODB/controlfile/o1_mf_m3bvy4p9_.ctl

Tạo mới TBS: => hệ thống sẽ báo lỗi không tạo được tbs do không có controlfile để ghi đường dẫn datafile của TBS mới.

sqlplus / as sysdba

create tablespace datalink_tbs datafile '/u01/app/oracle/oradata/DUODB/datafile/datalink_tbs01.dbf' size 100M;

SQL> create tablespace datalink_tbs datafile ‘/u01/app/oracle/oradata/DUODB/datafile/datalink_tbs01.dbf’ size 100M;

create tablespace datalink_tbs datafile ‘/u01/app/oracle/oradata/DUODB/datafile/datalink_tbs01.dbf’ size 100M

*

ERROR at line 1:

ORA-00210: cannot open the specified control file

ORA-00202: control file:

‘/u01/app/oracle/oradata/DUODB/controlfile/o1_mf_m3bvy4o4_.ctl’

ORA-27041: unable to open file

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

Additional information: 3

Shut instance và startup nomount: (có thể phải shutdown abort do không còn controlfile nên không thể shutdown immediate được nữa)

shut immediate;

shut abort;

startup nomount ;

SQL> shut immediate;

ORA-00210: cannot open the specified control file

ORA-00202: control file: ‘/u01/app/oracle/oradata/DUODB/controlfile/o1_mf_m3bvy4o4_.ctl’

ORA-27041: unable to open file

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

Additional information: 3

SQL> shut abort;

ORACLE instance shut down.

SQL> startup nomount ;

ORACLE instance started.

Total System Global Area 1828713632 bytes

Fixed Size 8897696 bytes

Variable Size 436207616 bytes

Database Buffers 1375731712 bytes

Redo Buffers 7876608 bytes

Tiến hành restore controlfile: dbid= 381617332 là số khi login vào Rman nó hiển thị trên màn hình

rman target /

set dbid=381617332

restore controlfile from autobackup;

alter database mount;

recover database;

alter database open resetlogs;

[oracle@srv1 datafile]$ rman target /

Recovery Manager: Release 19.0.0.0.0 – Production on Fri May 10 23:46:55 2024

Version 19.3.0.0.0

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

connected to target database: DUODB (not mounted)

RMAN> set dbid=381617332

executing command: SET DBID

RMAN> restore controlfile from autobackup;

Starting restore at 10-MAY-24

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=21 device type=DISK

recovery area destination: /u01/app/oracle/fast_recovery_area

database name (or database unique name) used for search: DUODB

channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/fast_recovery_area/DUODB/autobackup/2024_05_10/o1_mf_s_1168645425_m3wmp20h_.bkp found in the recovery area

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20240510

channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/fast_recovery_area/DUODB/autobackup/2024_05_10/o1_mf_s_1168645425_m3wmp20h_.bkp

channel ORA_DISK_1: control file restore from AUTOBACKUP complete

output file name=/u01/app/oracle/oradata/DUODB/controlfile/o1_mf_m3bvy4o4_.ctl

output file name=/u01/app/oracle/fast_recovery_area/DUODB/controlfile/o1_mf_m3bvy4p9_.ctl

Finished restore at 10-MAY-24

RMAN> alter database mount;

released channel: ORA_DISK_1

Statement processed

RMAN> recover database;

Starting recover at 10-MAY-24

Starting implicit crosscheck backup at 10-MAY-24

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=24 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=259 device type=DISK

allocated channel: ORA_DISK_3

channel ORA_DISK_3: SID=25 device type=DISK

allocated channel: ORA_DISK_4

channel ORA_DISK_4: SID=260 device type=DISK

allocated channel: ORA_DISK_5

channel ORA_DISK_5: SID=26 device type=DISK

allocated channel: ORA_DISK_6

channel ORA_DISK_6: SID=261 device type=DISK

allocated channel: ORA_DISK_7

channel ORA_DISK_7: SID=27 device type=DISK

allocated channel: ORA_DISK_8

channel ORA_DISK_8: SID=262 device type=DISK

allocated channel: ORA_DISK_9

channel ORA_DISK_9: SID=28 device type=DISK

allocated channel: ORA_DISK_10

channel ORA_DISK_10: SID=263 device type=DISK

Crosschecked 4 objects

Crosschecked 4 objects

Crosschecked 4 objects

Crosschecked 5 objects

Crosschecked 5 objects

Crosschecked 5 objects

Crosschecked 5 objects

Crosschecked 5 objects

Crosschecked 5 objects

Crosschecked 5 objects

Finished implicit crosscheck backup at 10-MAY-24

Starting implicit crosscheck copy at 10-MAY-24

using channel ORA_DISK_1

using channel ORA_DISK_2

using channel ORA_DISK_3

using channel ORA_DISK_4

using channel ORA_DISK_5

using channel ORA_DISK_6

using channel ORA_DISK_7

using channel ORA_DISK_8

using channel ORA_DISK_9

using channel ORA_DISK_10

Finished implicit crosscheck copy at 10-MAY-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/DUODB/autobackup/2024_05_10/o1_mf_s_1168645425_m3wmp20h_.bkp

using channel ORA_DISK_1

using channel ORA_DISK_2

using channel ORA_DISK_3

using channel ORA_DISK_4

using channel ORA_DISK_5

using channel ORA_DISK_6

using channel ORA_DISK_7

using channel ORA_DISK_8

using channel ORA_DISK_9

using channel ORA_DISK_10

starting media recovery

archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/fast_recovery_area/DUODB/onlinelog/o1_mf_1_m3v6f7ld_.log

archived log file name=/u01/app/oracle/fast_recovery_area/DUODB/onlinelog/o1_mf_1_m3v6f7ld_.log thread=1 sequence=7

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

Finished recover at 10-MAY-24

RMAN> alter database open resetlogs;

Statement processed

Kiểm tra controlfile: => đã có file được tạo lại từ autobackup

ls /u01/app/oracle/oradata/DUODB/controlfile/o1_mf_m3bvy4o4_.ctl

ls /u01/app/oracle/fast_recovery_area/DUODB/controlfile/o1_mf_m3bvy4p9_.ctl

[oracle@srv1 ~]$ ls /u01/app/oracle/oradata/DUODB/controlfile/o1_mf_m3bvy4o4_.ctl

/u01/app/oracle/oradata/DUODB/controlfile/o1_mf_m3bvy4o4_.ctl

[oracle@srv1 ~]$ ls /u01/app/oracle/fast_recovery_area/DUODB/controlfile/o1_mf_m3bvy4p9_.ctl

/u01/app/oracle/fast_recovery_area/DUODB/controlfile/o1_mf_m3bvy4p9_.ctl

Chúc các bạn thành công.

Xem thêm:

  1. Rman Restore database khi mất Online Redo logfile Oracle Database 19c
  2. Rman Restore Tablespace trong khi mất datafile và không có datafile backup Oracle Database 19c
  3. Restore Oracle Database to New Server using Rman Oracle Database 19c – Clone database
  4. Recover khi mất REDO log files
  5. Duplicate Database using Rman Oracle Database 19c
  6. Rman backup database tự động sử dụng Script shell và crontab Oracle database 19c
  7. Demo Tự động Flashback Standby database khi Flashback trên Primary Oracle Data Guard 19c
  8. Rman Oracle Block Change Tracking Oracle Database 19c
  9. Rebuild Oracle Data Guard sau khi FAILOVER sử dụng DGMGRL Utility Oracle Data Guard 19c
  10. Rman Demo Recovery Table Point in time Oracle database 19c

Datalinks.vn

Hello các bạn, tôi là Dương Nguyễn (DuoDBA), là người đang tập tành viết lách trên blog này. Mong muốn được chia sẻ kiến thức và kinh nghiệm về cơ sở dữ liệu với những người đam mê và quan tâm đến lĩnh vực này. #Tôi có tổ chức các khoá Coaching về #OracleDatabase và luyện thi #OCP thường xuyên, các bạn cần người đồng hành thì alo tôi nhé. Call/Zalo: 0765 871 888. Thanks you !.....
0 0 đánh giá
Đánh giá bài viết
Theo dõi
Thông báo của
guest

0 Góp ý
Phản hồi nội tuyến
Xem tất cả bình luận