[Oracle DB] Phục hồi database trong trường hợp mất controlfile, datafile từ bản backup
===
-- Phục hồi khi mất controlfile:
SQL> show parameter control_file;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/CDB1/control01.ctl, /u01/app/oracle/oradata/CDB1/control02.ctl
--####################################
oracle@db2 10.0.2.15:CDB1$ export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
oracle@db2 10.0.2.15:CDB1$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Apr 23 22:36:55 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: CDB1 (DBID=1136356795)
RMAN> list backup;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
16 Incr 0 2.50G DISK 00:04:47 2024-04-23 01:51:40
BP Key: 16 Status: AVAILABLE Compressed: YES Tag: LEVEL0
Piece Name: /u01/backup/level0/data_file_20240423_CDB1_0h2oubcd_17
List of Datafiles in backup set 16
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
1 0 Incr 10588021 2024-04-23 01:46:53 NO /u01/app/oracle/oradata/CDB1/system01.dbf
3 0 Incr 10588021 2024-04-23 01:46:53 NO /u01/app/oracle/oradata/CDB1/sysaux01.dbf
5 0 Incr 10588021 2024-04-23 01:46:53 NO /u01/app/oracle/oradata/CDB1/undotbs01.dbf
7 0 Incr 10588021 2024-04-23 01:46:53 NO /u01/app/oracle/oradata/CDB1/users01.dbf
25 0 Incr 10588021 2024-04-23 01:46:53 NO /u01/app/oracle/oradata/CDB1/DATA.DBF
70 0 Incr 10588021 2024-04-23 01:46:53 NO /u01/app/oracle/oradata/CDB1/indx.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
17 Incr 0 117.91M DISK 00:00:12 2024-04-23 01:52:01
BP Key: 17 Status: AVAILABLE Compressed: YES Tag: LEVEL0
Piece Name: /u01/backup/level0/data_file_20240423_CDB1_0i2oubll_18
List of Datafiles in backup set 17
Container ID: 2, PDB Name: PDB$SEED
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
2 0 Incr 1682527 2024-03-03 13:53:30 NO /u01/app/oracle/oradata/CDB1/pdbseed/system01.dbf
4 0 Incr 1682527 2024-03-03 13:53:30 NO /u01/app/oracle/oradata/CDB1/pdbseed/sysaux01.dbf
6 0 Incr 1682527 2024-03-03 13:53:30 NO /u01/app/oracle/oradata/CDB1/pdbseed/undotbs01.dbf
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
19 104.24M DISK 00:00:08 2024-04-23 01:52:14
BP Key: 19 Status: AVAILABLE Compressed: YES Tag: ARCH
Piece Name: /u01/backup/level0/arc_20240423_CDB1_0k2oubm6_20_0k2oubm6_1_1
List of Archived Logs in backup set 19
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 539 10557312 2024-04-22 21:56:34 10576391 2024-04-22 22:23:34
1 540 10576391 2024-04-22 22:23:34 10576399 2024-04-22 22:23:36
1 541 10576399 2024-04-22 22:23:36 10577286 2024-04-22 22:44:07
1 542 10577286 2024-04-22 22:44:07 10579536 2024-04-22 23:21:14
1 543 10579536 2024-04-22 23:21:14 10579544 2024-04-22 23:21:17
1 544 10579544 2024-04-22 23:21:17 10588303 2024-04-23 01:52:05
1 545 10588303 2024-04-23 01:52:05 10588311 2024-04-23 01:52:05
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
20 Full 17.95M DISK 00:00:00 2024-04-23 01:52:21
BP Key: 20 Status: AVAILABLE Compressed: NO Tag: TAG20240423T015221
Piece Name: /u01/backup/level0/control_file_c-1136356795-20240423-01
SPFILE Included: Modification time: 2024-04-22 20:41:55
SPFILE db_unique_name: CDB1
Control File Included: Ckp SCN: 10588328 Ckp time: 2024-04-23 01:52:21
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
21 Incr 1 52.59M DISK 00:02:45 2024-04-23 02:03:15
BP Key: 21 Status: AVAILABLE Compressed: YES Tag: LEVEL1
Piece Name: /u01/backup/level1/data_file_20240423_CDB1_0m2ouc5u_22
List of Datafiles in backup set 21
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
1 1 Incr 10589367 2024-04-23 02:00:30 NO /u01/app/oracle/oradata/CDB1/system01.dbf
3 1 Incr 10589367 2024-04-23 02:00:30 10589547 NO /u01/app/oracle/oradata/CDB1/sysaux01.dbf
5 1 Incr 10589367 2024-04-23 02:00:30 NO /u01/app/oracle/oradata/CDB1/undotbs01.dbf
7 1 Incr 10589367 2024-04-23 02:00:30 NO /u01/app/oracle/oradata/CDB1/users01.dbf
25 1 Incr 10589367 2024-04-23 02:00:30 NO /u01/app/oracle/oradata/CDB1/DATA.DBF
70 1 Incr 10589367 2024-04-23 02:00:30 NO /u01/app/oracle/oradata/CDB1/indx.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
22 Full 17.95M DISK 00:00:00 2024-04-23 02:03:22
BP Key: 22 Status: AVAILABLE Compressed: NO Tag: TAG20240423T020322
Piece Name: /u01/backup/level1/control_file_c-1136356795-20240423-02
SPFILE Included: Modification time: 2024-04-22 20:41:55
SPFILE db_unique_name: CDB1
Control File Included: Ckp SCN: 10589671 Ckp time: 2024-04-23 02:03:22
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
23 176.15M DISK 00:00:15 2024-04-23 02:03:41
BP Key: 23 Status: AVAILABLE Compressed: YES Tag: ARCH
Piece Name: /u01/backup/level1/arc_20240423_CDB1_0p2oucbe_25_0p2oucbe_1_1
List of Archived Logs in backup set 23
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 544 10579544 2024-04-22 23:21:17 10588303 2024-04-23 01:52:05
1 545 10588303 2024-04-23 01:52:05 10588311 2024-04-23 01:52:05
1 546 10588311 2024-04-23 01:52:05 10588655 2024-04-23 01:56:09
1 547 10588655 2024-04-23 01:56:09 10588805 2024-04-23 01:56:30
1 548 10588805 2024-04-23 01:56:30 10588930 2024-04-23 01:56:50
1 549 10588930 2024-04-23 01:56:50 10589686 2024-04-23 02:03:24
1 550 10589686 2024-04-23 02:03:24 10589694 2024-04-23 02:03:25
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
24 Full 17.95M DISK 00:00:00 2024-04-23 02:03:51
BP Key: 24 Status: AVAILABLE Compressed: NO Tag: TAG20240423T020351
Piece Name: /u01/backup/level1/control_file_c-1136356795-20240423-03
SPFILE Included: Modification time: 2024-04-22 20:41:55
SPFILE db_unique_name: CDB1
Control File Included: Ckp SCN: 10589716 Ckp time: 2024-04-23 02:03:51
--####################################
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 4647288304 bytes
Fixed Size 9145840 bytes
Variable Size 1107296256 bytes
Database Buffers 3523215360 bytes
Redo Buffers 7630848 bytes
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info
--####################################
oracle@db2 10.0.2.15:CDB1$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Apr 23 23:14:21 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: CDB1 (not mounted)
RMAN> list backup;
using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 04/23/2024 23:14:26
ORA-01507: database not mounted
RMAN> restore controlfile from '/u01/backup/level1/control_file_c-1136356795-20240423-04';
RMAN> restore controlfile from '/u01/backup/level1/control_file_c-1136356795-20240423-02' to '/u01/app/oracle/oradata/CDB1/control02.ctl';
RUN {
ALLOCATE CHANNEL ch1 DEVICE TYPE DISK;
ALLOCATE CHANNEL ch2 DEVICE TYPE DISK;
ALLOCATE CHANNEL ch3 DEVICE TYPE DISK;
RESTORE DATABASE;
RECOVER DATABASE;
RELEASE CHANNEL ch1;
RELEASE CHANNEL ch2;
RELEASE CHANNEL ch3;
}
SQL> alter database open resetlogs;===
-- Phục hồi khi mất một trong các datafile:
SQL> select FILE_NAME, TABLESPACE_NAME from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u01/app/oracle/oradata/CDB1/system01.dbf
SYSTEM
/u01/app/oracle/oradata/CDB1/sysaux01.dbf
SYSAUX
/u01/app/oracle/oradata/CDB1/undotbs01.dbf
UNDOTBS1
FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u01/app/oracle/oradata/CDB1/users01.dbf
USERS
/u01/app/oracle/oradata/CDB1/DATA.DBF
DATA
/u01/app/oracle/oradata/CDB1/indx.dbf
INDX
6 rows selected.
--####################################
oracle@db2 10.0.2.15:CDB1$ ll
total 26G
drwxr-x--- 3 oracle oinstall 4.0K Apr 24 00:49 .
drwxr-x--- 3 oracle oinstall 18 Mar 3 11:22 ..
-rw-r----- 1 oracle oinstall 18M Apr 24 00:49 control01.ctl
-rw-r----- 1 oracle oinstall 18M Apr 24 00:49 control02.ctl
-rw-r----- 1 oracle oinstall 21G Apr 24 00:16 DATA.DBF
-rw-r----- 1 oracle oinstall 1.1M Apr 24 00:16 indx.dbf
drwxr-x--- 2 oracle oinstall 85 Mar 3 11:23 pdbseed
-rw-r----- 1 oracle oinstall 201M Apr 24 00:49 redo01.log
-rw-r----- 1 oracle oinstall 201M Apr 24 00:16 redo02.log
-rw-r----- 1 oracle oinstall 201M Apr 24 00:16 redo03.log
-rw-r----- 1 oracle oinstall 967M Apr 24 00:47 sysaux01.dbf
-rw-r----- 1 oracle oinstall 936M Apr 24 00:44 system01.dbf
-rw-r----- 1 oracle oinstall 1.2G Apr 23 22:20 temp01.dbf
-rw-r----- 1 oracle oinstall 2.0G Apr 24 00:47 undotbs01.dbf
-rw-r----- 1 oracle oinstall 3.1M Apr 24 00:16 users01.dbf
oracle@db2 10.0.2.15:CDB1$ mv ./system01.dbf ./xxx.system01.dbf
--####################################
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/CDB1/system01.dbf'
--####################################
RUN {
ALLOCATE CHANNEL ch1 DEVICE TYPE DISK;
ALLOCATE CHANNEL ch2 DEVICE TYPE DISK;
ALLOCATE CHANNEL ch3 DEVICE TYPE DISK;
ALTER DATABASE DATAFILE 1 OFFLINE;
RESTORE DATAFILE 1;
RECOVER DATAFILE 1;
ALTER DATABASE DATAFILE 1 ONLINE;
RELEASE CHANNEL ch1;
RELEASE CHANNEL ch2;
RELEASE CHANNEL ch3;
}
--####################################
SQL> alter database open;
Database altered.===
Nhận xét
Đăng nhận xét