Thứ Ba, 4 tháng 8, 2020

Quy trình khôi phục cơ sở dữ liệu Oracle sang host mới

Bài viết hướng dẫn tạo bản copy của CSDL trên host mới. Trong trường hợp này DBID của CSDL trên host mới sẽ giống với trên host cũ. Nhưng nếu mục tiêu là tạo ra bản copy mới của cơ sở dữ liệu(CSDL) dùng RMAN DUPLICATE; điều này sẽ thay đổI DBID của CSDL trên host mới.
Trong ví dụ này, CSDL ban đầu tên là ORCL chạy trên máy OEL1. Tôi sẽ thực hiện backup trên máy OEL1, copy bản backup sang máy OEL2; thực hiện restore và recover trên máy OEL2.
Tóm tắt
 1. Cài đặt lại CSDL trên server mới.
2. Chỉnh sửa kết nối của các thành phần manager, station, web đến địa chỉ IP, Port và SID mới
3. Thực hiện restore CSDL từ bản backup:
    $ rman target /
    RMAN> set dbid 1283774808
    RMAN> restore spfile to pfile '/u01/app/oracle/product/11.1.0/db_1/dbs/initORCL.ora'  from '/home/oracle/ORCL_backup/o1_mf_ncsnf_TAG20120415T081939_7rn8vhyx_.bkp';
    RMAN> startup force nomount pfile='/u01/app/oracle/product/11.1.0/db_1/dbs/initORCL.ora';
    RMAN> catalog start with '/u01/app/oracle/backupdb/backupset';  --Thư mục chứa bản backup copy về
    RMAN> restore controlfile from '/u01/app/oracle/backupdb/backupset/autobackup_control_filec-3739803323-20170717-00';
    RMAN> ALTER DATABASE MOUNT;
    RMAN> RESTORE DATABASE;
    • Xóa redolog cũ để hệ thống tự tạo mới từ đầu
    $ rm -rf /u01/app/oracle/oradata/PTPM/redo*
    RMAN> RECOVER DATABASE NOREDO;
    RMAN> ALTER DATABASE OPEN RESETLOGS;
4. Kết nối vào DB kiểm tra DB hoạt động bình thường và đầy đủ dữ liệu.
1. Trên máy OEL1 (máy nguồn)
1.1 Thực hiện backup Parameter file, Control files và toàn bộ Data files.
[oracle@oel1 ~]$ rman target /
Recovery Manager: Release 11.1.0.6.0 - Production on Sun Apr 15 08:19:26 2012
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
connected to target database: ORCL (DBID=1283774808)
RMAN> backup database;
Starting backup at 15-APR-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=137 device type=DISK
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/ORCL/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/ORCL/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/ORCL/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ORCL/users01.dbf
channel ORA_DISK_1: starting piece 1 at 15-APR-12
channel ORA_DISK_1: finished piece 1 at 15-APR-12
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2012_04_15/o1_mf_nnndf_TAG20120415T081939_7rn8m0d6_.bkp
tag=TAG20120415T081939 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:04:21
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 set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 15-APR-12
channel ORA_DISK_1: finished piece 1 at 15-APR-12
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2012_04_15/o1_mf_ncsnf_TAG20120415T081939_7rn8vhyx_.bkp
tag=TAG20120415T081939 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished backup at 15-APR-12
RMAN>
1.2 Copy bản backup trên sang máy OEL2
[oracle@oel1 ~]$ scp /u01/app/oracle/flash_recovery_area/ORCL/backupset/
2012_04_15/o1_mf_nnndf_TAG20120415T081939_7rn8m0d6_.bkp oracle@OEL2:/ORCL_backup/
Password:
o1_mf_nnndf_TAG20120 100% |***********************************************| 1120 MB 00:59
[oracle@oel1 ~]$ scp /u01/app/oracle/flash_recovery_area/ORCL/backupset/2012_04_15/
o1_mf_ncsnf_TAG20120415T081939_7rn8vhyx_.bkp oracle@OEL2:/ORCL_backup/
Password:
o1_mf_ncsnf_TAG20120 100% |***********************************************| 940 KB 00:00

1.3 Xác định DBID của CSDL nguồn
Ở bước đầu tiên khi dùng RMAN đã thấy xuất hiện DBID
connected to target database: ORCL (DBID=1283774808)
Hoặc có thể dùng lệnh sau đây
[oracle@oel5 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Sun Apr 15 08:43:13 2012
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select dbid from v$database;
DBID
----------
1283774808
SQL>
2. Trên máy OEL2 (máy đích)
2.1 Gán DBID và restore spfile vào pfile
[oracle@oel5 ~]$ rman target /
Recovery Manager: Release 11.1.0.6.0 - Production on Sun Apr 15 08:54:06 2012
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
connected to target database (not started)
RMAN> set dbid 1283774808
executing command: SET DBID
RMAN> startup nomount
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.1.0/db_1/dbs/initORCL.ora'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area     159019008 bytes
Fixed Size                     1298584 bytes
Variable Size                 67112808 bytes
Database Buffers              83886080 bytes
Redo Buffers                   6721536 bytes
RMAN> restore spfile to pfile '/u01/app/oracle/product/11.1.0/db_1/dbs/initORCL.ora'
from '/home/oracle/ORCL_backup/o1_mf_ncsnf_TAG20120415T081939_7rn8vhyx_.bkp';
Starting restore at 15-APR-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=98 device type=DISK
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /home/oracle/ORCL_backup/o1_mf_ncsnf_TAG20120415T081939_7rn8vhyx_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 15-APR-12
RMAN>
2.2 Startup instance với pfile
Trước tiên, mở tập tin pfile và tạo các thư mục audit_file_dest, control_files,.. nếu chưa tồn tại hoặc thay đổi đường dẫn cho phù hợp.

[oracle@oel5 ~]$ vi /u01/app/oracle/product/11.1.0/db_1/dbs/initORCL.ora
ORCL.__db_cache_size=159383552
ORCL.__java_pool_size=12582912
ORCL.__large_pool_size=4194304
ORCL.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
ORCL.__pga_aggregate_target=117440512
ORCL.__sga_target=348127232
ORCL.__shared_io_pool_size=0
ORCL.__shared_pool_size=163577856
ORCL.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/ORCL/adump'
*.audit_trail='db'
*.compatible='11.1.0.0.0'
*.control_files='/u01/app/oracle/oradata/ORCL/control01.ctl','/u01/app/oracle/oradata/ORCL/control02.ctl',
'/u01/app/oracle/oradata/ORCL/control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='ORCL'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4294967296
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=115343360
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=348127232
*.undo_tablespace='UNDOTBS1'
Tiếp theo mớI startup instance
RMAN> startup force nomount pfile='/u01/app/oracle/product/11.1.0/db_1/dbs/initORCL.ora';
Oracle instance started
Total System Global Area     347340800 bytes
Fixed Size                     1299848 bytes
Variable Size                180357752 bytes
Database Buffers             159383552 bytes
Redo Buffers                   6299648 bytes
RMAN>
2.3 Restore control files và mount database
RMAN> restore controlfile from '/home/oracle/ORCL_backup/o1_mf_ncsnf_TAG20120415T081939_7rn8vhyx_.bkp';
Starting restore at 15-APR-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=153 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u01/app/oracle/oradata/ORCL/control01.ctl
output file name=/u01/app/oracle/oradata/ORCL/control02.ctl
output file name=/u01/app/oracle/oradata/ORCL/control03.ctl
Finished restore at 15-APR-12
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN>
2.4 Xác định Data files và Redo log files
[oracle@oel5 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Sun Apr 15 09:15:29 2012
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> column name format a60
SQL> select file# as "file/grp#", name from v$datafile
  2  union
  3  select group#, member from v$logfile
  4  /
file/grp# NAME
---------- ------------------------------------------------------------
         1 /u01/app/oracle/oradata/ORCL/redo01.log
         1 /u01/app/oracle/oradata/ORCL/system01.dbf
         2 /u01/app/oracle/oradata/ORCL/redo02.log
         2 /u01/app/oracle/oradata/ORCL/sysaux01.dbf
         3 /u01/app/oracle/oradata/ORCL/redo03.log
         3 /u01/app/oracle/oradata/ORCL/undotbs01.dbf
         4 /u01/app/oracle/oradata/ORCL/users01.dbf
         5 /u01/app/oracle/oradata/ORCL/example01.dbf
8 rows selected.
SQL>
2.5. Catalog backuppiece
RMAN> catalog backuppiece '/home/oracle/ORCL_backup/o1_mf_nnndf_TAG20120415T081939_7rn8m0d6_.bkp';
Starting implicit crosscheck backup at 15-APR-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=153 device type=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 15-APR-12
Starting implicit crosscheck copy at 15-APR-12
using channel ORA_DISK_1
Finished implicit crosscheck copy at 15-APR-12
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_24/o1_mf_1_4_708x39cm_.arc
File Name: /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_24/o1_mf_1_2_708vtnfv_.arc
File Name: /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_24/o1_mf_1_3_708w2dby_.arc
File Name: /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2011_07_06/o1_mf_1_7_718ft7y2_.arc
File Name: /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_25/o1_mf_1_6_70cc2x2v_.arc
File Name: /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_25/o1_mf_1_5_70c9jww1_.arc
File Name: /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2011_07_08/o1_mf_1_8_71g7bvdl_.arc
File Name: /u01/app/oracle/flash_recovery_area/ORCL/backupset/2012_04_15/o1_mf_ncsnf_TAG20120415T081939_7rn8vhyx_.bkp
cataloged backup piece
backup piece handle=/home/oracle/ORCL_backup/o1_mf_nnndf_TAG20120415T081939_7rn8m0d6_.bkp RECID=3 STAMP=780657473
RMAN> list backup;
List of Backup Sets
===================
BS Key  Type LV Size
------- ---- -- ----------
1       Full    1.04G
  List of Datafiles in backup set 1
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 697670     15-APR-12 /u01/app/oracle/oradata/ORCL/system01.dbf
  2       Full 697670     15-APR-12 /u01/app/oracle/oradata/ORCL/sysaux01.dbf
  3       Full 697670     15-APR-12 /u01/app/oracle/oradata/ORCL/undotbs01.dbf
  4       Full 697670     15-APR-12 /u01/app/oracle/oradata/ORCL/users01.dbf
  5       Full 697670     15-APR-12 /u01/app/oracle/oradata/ORCL/example01.dbf
Backup Set Copy #1 of backup set 1
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  DISK        00:05:01     15-APR-12       NO         TAG20120415T081939
List of Backup Pieces for backup set 1 Copy #1
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    1       1   AVAILABLE   /u01/app/oracle/flash_recovery_area/ORCL/backupset/2012_04_15/o1_mf_nnndf_TAG20120415T081939_7rn8m0d6_.bkp
Backup Set Copy #2 of backup set 1
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  DISK        00:05:01     15-APR-12       NO         TAG20120415T081939
List of Backup Pieces for backup set 1 Copy #2
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    3       1   AVAILABLE   /home/oracle/ORCL_backup/o1_mf_nnndf_TAG20120415T081939_7rn8m0d6_.bkp
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Full    9.36M      DISK        00:00:00     15-APR-12     
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20120415T081939
        Piece Name: /u01/app/oracle/flash_recovery_area/ORCL/backupset/2012_04_15/o1_mf_ncsnf_TAG20120415T081939_7rn8vhyx_.bkp
  SPFILE Included: Modification time: 15-APR-12
  SPFILE db_unique_name: ORCL
  Control File Included: Ckp SCN: 697995       Ckp time: 15-APR-12
RMAN>
2.6 Build script để recover database
RMAN> run {
    set newname for datafile 1 to '/u01/app/oracle/oradata/ORCL/system01.dbf';
    set newname for datafile 2 to '/u01/app/oracle/oradata/ORCL/sysaux01.dbf';
    set newname for datafile 3 to '/u01/app/oracle/oradata/ORCL/undotbs01.dbf';
    set newname for datafile 4 to '/u01/app/oracle/oradata/ORCL/users01.dbf';
    set newname for datafile 5 to '/u01/app/oracle/oradata/ORCL/example01.dbf';
   
    set until scn 697670;
    restore database;
    switch datafile all;
    recover database;
}
2.7 Open database resetlogs
RMAN> alter database open resetlogs;

database opened
@ Trần Văn Bình - Founder of "Oracle DBA Việt Nam" #OracleTutorial #OracleDBA #OracleDatabaseAdministration #học oracle database #oca #ocp #oce #ocm
By lúc
Labels: ,

ĐỌC NHIỀU
Trần Văn Bình - Oracle Database Master