Thực hành Sử dụng RMAN để Duplicate Database

Thực hành: Sử dụng RMAN để Duplicate Database

Trong thực tế có rất nhiều trường hợp chúng ta cần phải Duplicate Database (Clone DB). Chẳng hạn như chúng ta cần DB để test chương trình mới, sử dụng để cho developer, dùng chạy reports,...

Có nhiều cách để Clone DB, ví dụ như cách Clone DB bằng Tay mà chúng ta từng tham khảo. Hôm nay chúng ta sẽ cùng Clone DB bằng một công cụ khác: RMAN

Các bước thực hiện bao gồm: 1. Chuẩn bị DB nguồn và server.2. Thiết lập kết nối (Oracle Net) đến DB nguồn.3. Tạo Parameter File (pfile) cho DB nhân bản.4. Tạo Password File cho DB nhân bản.5. Startup DB nhân bản ở bước NOMOUNT.6. Dùng RMAN để full backup DB nguồn.7. Chạy lệnh Duplicate.

1. Chuẩn bị DB nguồn và server.

Cấu hình DB và OS trong bài này:- OS: Linux CentOS 4- DB: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0- Server Name: labs

- DB nguồn: orcl+ Archive Mode

- DB nhân bản: oravn

- Backup Dir: /u01/oravn_backup/- ORACLE_HOME: /u01/app/oracle/product/10.2.0/db_1/

2. Thiết lập kết nối (Oracle Net) đến DB nguồn.

- Mở file /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.orathêm vào đọan kết nối như bên dưới:

ORCL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = labs)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl) ) )

- Thử nghiệm kết nối bằng tnsping và bằng sqlplus

[oracle@labs ~]$ tnsping orclTNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 27-APR-2009 09:28:32

Copyright (c) 1997, 2005, Oracle. All rights reserved.Used parameter files:

Used TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = labs) (PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))OK (10 msec)[oracle@labs ~]$ [oracle@labs ~]$ sqlplus sys/oracle@orcl as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Apr 27 09:30:18 2009Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning and Data Mining options

SQL>

- Để tìm hiểu cấi hình Oracle Net, vui lòng xem ở đây.

3. Tạo Parameter File (pfile) cho DB nhân bản.

Cách đơn giản để tạo pfile cho DB nhân bản là "copy" nội dung từ spfile của DB nguồn thông qua lệnh CREATE PFILE..., sau đó sửa lại.

- Copy từ spfile của DB nguồn:

[oracle@labs ~]$ export ORACLE_SID=orcl[oracle@labs ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Apr 28 04:25:46 2009

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


SQL> create pfile='/u01/oravn_backup/initoravn.ora' from spfile ;

File created.

SQL> exitDisconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning and Data Mining options[oracle@labs ~]$ [oracle@labs ~]$ ls -lh /u01/oravn_backup/initoravn.ora -rw-r--r-- 1 oracle dba 1.1K Apr 27 09:49 /u01/oravn_backup/initoravn.ora[oracle@labs ~]$

- Sửa lại một chút trong file trên: + Thay các dường dẫn đến orcl thành oravn+ Thêm dòng convert đường dẫn sang nơi khác.

<i>Bên trái là nội dung pfile cũ, bên phải là nội dung sau khi sửa lại.</i>

-Lưu ý: tạo các đường dẫn tương ứng trong file trên:

[oracle@labs ~]$ mkdir -p /u01/app/oracle/admin/oravn/adump [oracle@labs ~]$ mkdir -p /u01/app/oracle/admin/oravn/bdump [oracle@labs ~]$ mkdir -p /u01/app/oracle/admin/oravn/cdump [oracle@labs ~]$ mkdir -p /u01/app/oracle/admin/oravn/udump [oracle@labs ~]$ mkdir -p /u01/app/oracle/oradata/oravn

4. Tạo Password File cho DB nhân bản.

- Dùng lệnh orapwd để tạo password file. Trong lệnh này tôi đặt password cho user SYS là oracle

[oracle@labs ~]$ orapwd file='/u01/app/oracle/product/10.2.0/db_1/dbs/orapworavn' password=oracle entries=5[oracle@labs ~]$ ls -lh /u01/app/oracle/product/10.2.0/db_1/dbs/orapworavn -rw-r----- 1 oracle dba 1.5K Nov 13 11:54 /u01/app/oracle/product/10.2.0/db_1/dbs/orapworavn

Lưu ý: + Trên Linux và Unix, tên file phân biệt chữ Hoa và chữ thường.+ Tên và vị trí pwfile phải nằm đúng thư mục qui ước tùy theo OS.

5. Startup DB nhân bản ở bước NOMOUNT.

Sau khi có đủ pfile và pwfile, chúng ta đã có thể STARTUP NOMOUNT DB nhân bản, đồng thời tạo luôn spfile từ pfile ở (bước 2).

[oracle@labs ~]$export ORACLE_SID=oravn[oracle@labs ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Apr 27 10:22:48 2009Copyright (c) 1982, 2005, Oracle. All rights reserved.Connected to an idle instance.

SQL> startup nomount pfile='/u01/oravn_backup/initoravn.ora' ;ORACLE instance started.

Total System Global Area 285212672 bytesFixed Size 1218992 bytesVariable Size 92276304 bytesDatabase Buffers 188743680 bytesRedo Buffers 2973696 bytesSQL> create spfile from pfile='/u01/oravn_backup/initoravn.ora' ;

File created.

SQL> exitDisconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning and Data Mining options[oracle@labs ~]$

Kiểm tra để chắc chắn là spfile cho oravn đã được tạo:

[oracle@labs ~]$ ls -l $ORACLE_HOME/dbs/sp*.ora-rw-r----- 1 oracle dba 2560 Apr 27 10:23/u01/app/oracle/product/10.2.0/db_1/dbs/spfileoravn.ora-rw-r----- 1 oracle dba 2560 Apr 27 06:51/u01/app/oracle/product/10.2.0/db_1/dbs/spfileorcl.ora[oracle@labs ~]$

6. Dùng RMAN để full backup DB nguồn.

Trong phần này chúng ta làm 2 việc:- Kiểm tra xem thông số CONTROLFILE AUTOBACKUP, và bật lên thành ON.- Full Backup Database cùng với Archive Log file.

[oracle@labs oravn_backup]$ rman

Recovery Manager: Release 10.2.0.1.0 - Production on Mon Apr 27 10:03:00 2009


RMAN> connect target sys/oracle@orcl

connected to target database: ORCL (DBID=1212807860)

RMAN> show all;

RMAN configuration parameters are:CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # defaultCONFIGURE BACKUP OPTIMIZATION OFF; # defaultCONFIGURE DEFAULT DEVICE TYPE TO DISK; # defaultCONFIGURE CONTROLFILE AUTOBACKUP OFF; # defaultCONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # defaultCONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # defaultCONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # defaultCONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # defaultCONFIGURE MAXSETSIZE TO UNLIMITED; # defaultCONFIGURE ENCRYPTION FOR DATABASE OFF; # defaultCONFIGURE ENCRYPTION ALGORITHM 'AES128'; # defaultCONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # defaultCONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/10.2.0/db_1/dbs/snapcf_orcl.f'; # default

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP on ;

new RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP ON;new RMAN configuration parameters are successfully stored

RMAN> backup database format '/u01/oravn_backup/db_%u' plus archivelog format'/u01/oravn_backup/arc_%u';

Starting backup at 27-APR-09current log archivedallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=148 devtype=DISKchannel ORA_DISK_1: starting archive log backupsetchannel ORA_DISK_1: specifying archive log(s) in backup setinput archive log thread=1 sequence=11 recid=1 stamp=685028724input archive log thread=1 sequence=12 recid=2 stamp=685028773input archive log thread=1 sequence=13 recid=3 stamp=685033763input archive log thread=1 sequence=14 recid=4 stamp=685262679input archive log thread=1 sequence=15 recid=5 stamp=685264476input archive log thread=1 sequence=16 recid=6 stamp=685264476input archive log thread=1 sequence=17 recid=7 stamp=685270847input archive log thread=1 sequence=18 recid=8 stamp=685274682channel ORA_DISK_1: starting piece 1 at 27-APR-09channel ORA_DISK_1: finished piece 1 at 27-APR-09piece handle=/u01/oravn_backup/arc_08kdgthr tag=TAG20090427T100442 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:04Finished backup at 27-APR-09

Starting backup at 27-APR-09using channel ORA_DISK_1channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00001 name=/u01/app/oracle/oradata/orcl/system01.dbfinput datafile fno=00003 name=/u01/app/oracle/oradata/orcl/sysaux01.dbfinput datafile fno=00005 name=/u01/app/oracle/oradata/orcl/example01.dbfinput datafile fno=00002 name=/u01/app/oracle/oradata/orcl/undotbs01.dbfinput datafile fno=00004 name=/u01/app/oracle/oradata/orcl/users01.dbfchannel ORA_DISK_1: starting piece 1 at 27-APR-09channel ORA_DISK_1: finished piece 1 at 27-APR-09piece handle=/u01/oravn_backup/db_09kdgti0 tag=TAG20090427T100447 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:55Finished backup at 27-APR-09

Starting backup at 27-APR-09current log archivedusing channel ORA_DISK_1channel ORA_DISK_1: starting archive log backupsetchannel ORA_DISK_1: specifying archive log(s) in backup setinput archive log thread=1 sequence=19 recid=9 stamp=685274743channel ORA_DISK_1: starting piece 1 at 27-APR-09channel ORA_DISK_1: finished piece 1 at 27-APR-09piece handle=/u01/oravn_backup/arc_0akdgtjn tag=TAG20090427T100543 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:02Finished backup at 27-APR-09

Starting Control File and SPFILE Autobackup at 27-APR-09piece handle=/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2009_04_27/o1_mf_s_685274746_4zb88bx3_.bkp comment=NONEFinished Control File and SPFILE Autobackup at 27-APR-09

RMAN> RMAN> list backup ;

List of Backup Sets===================

BS Key Size Device Type Elapsed Time Completion Time------- ---------- ----------- ------------ ---------------8 24.43M DISK 00:00:02 27-APR-09 BP Key: 8 Status: AVAILABLE Compressed: NO Tag: TAG20090427T100442Piece Name: /u01/oravn_backup/arc_08kdgthr

List of Archived Logs in backup set 8Thrd Seq Low SCN Low Time Next SCN Next Time---- ------- ---------- --------- ---------- ---------

1 11 474684 24-APR-09 478439 24-APR-091 12 478439 24-APR-09 478462 24-APR-091 13 478462 24-APR-09 498555 24-APR-091 14 498555 24-APR-09 522289 27-APR-091 15 522289 27-APR-09 532775 27-APR-091 16 532775 27-APR-09 532833 27-APR-091 17 532833 27-APR-09 535826 27-APR-091 18 535826 27-APR-09 537654 27-APR-09

BS Key Type LV Size Device Type Elapsed Time Completion Time------- ---- -- ---------- ----------- ------------ ---------------9 Full 570.48M DISK 00:00:47 27-APR-09 BP Key: 9 Status: AVAILABLE Compressed: NO Tag: TAG20090427T100447Piece Name: /u01/oravn_backup/db_09kdgti0List of Datafiles in backup set 9File LV Type Ckp SCN Ckp Time Name---- -- ---- ---------- --------- ----1 Full 537660 27-APR-09 /u01/app/oracle/oradata/orcl/system01.dbf2 Full 537660 27-APR-09 /u01/app/oracle/oradata/orcl/undotbs01.dbf3 Full 537660 27-APR-09 /u01/app/oracle/oradata/orcl/sysaux01.dbf4 Full 537660 27-APR-09 /u01/app/oracle/oradata/orcl/users01.dbf5 Full 537660 27-APR-09 /u01/app/oracle/oradata/orcl/example01.dbf

BS Key Size Device Type Elapsed Time Completion Time------- ---------- ----------- ------------ ---------------10 6.50K DISK 00:00:01 27-APR-09 BP Key: 10 Status: AVAILABLE Compressed: NO Tag: TAG20090427T100543Piece Name: /u01/oravn_backup/arc_0akdgtjn

List of Archived Logs in backup set 10Thrd Seq Low SCN Low Time Next SCN Next Time---- ------- ---------- --------- ---------- ---------1 19 537654 27-APR-09 537681 27-APR-09

BS Key Type LV Size Device Type Elapsed Time Completion Time------- ---- -- ---------- ----------- ------------ ---------------11 Full 6.80M DISK 00:00:01 27-APR-09 BP Key: 11 Status: AVAILABLE Compressed: NO Tag: TAG20090427T100546Piece Name: /u01/app/oracle/flash_recovery_area/ORCL/autobackup/2009_04_27/o1_mf_s_685274746_4zb88bx3_.bkpControl File Included: Ckp SCN: 537687 Ckp time: 27-APR-09SPFILE Included: Modification time: 27-APR-09

RMAN> RMAN> exit

Recovery Manager complete.[oracle@labs oravn_backup]$[oracle@labs oravn_backup]$ ls -lhtotal 596M-rw-r----- 1 oracle dba 25M Apr 27 10:04 arc_08kdgthr-rw-r----- 1 oracle dba 7.0K Apr 27 10:05 arc_0akdgtjn-rw-r----- 1 oracle dba 571M Apr 27 10:05 db_09kdgti0-rw-r--r-- 1 oracle dba 1.1K Apr 27 09:49 initoravn.ora[oracle@labs oravn_backup]$

7. Chạy lệnh Duplicate.

Sau khi đã xong các bước chuẩn bị, giờ chúng ta có thể Duplicate được DB rồi.

- Để Duplicate, chúng ta cần:+ connect vào DB nguồn đang OPEN (để MOUNT cũng oki).+ connect vào DB nhân bản (auxiliary) đang NOMOUNT.

[oracle@labs oravn_backup]$ export ORACLE_SID=oravn[oracle@labs oravn_backup]$ rman

Recovery Manager: Release 10.2.0.1.0 - Production on Mon Apr 27 10:25:08 2009


RMAN> connect target sys/oracle@orcl

connected to target database: ORCL (DBID=1212807860)

RMAN> connect auxiliary / ;

connected to auxiliary database: ORAVN (not mounted)

RMAN>

- Giờ chạy lệnh DUPLICATE được rồi !

RMAN> run {2> ALLOCATE AUXILIARY CHANNEL oravn DEVICE TYPE DISK ;3> DUPLICATE TARGET DATABASE to oravn;4> }

using target database control file instead of recovery catalogallocated channel: oravnchannel oravn: sid=156 devtype=DISK

Starting Duplicate Db at 27-APR-09

contents of Memory Script:{set until scn 537681;set newname for datafile 1 to "/u01/app/oracle/oradata/oravn/system01.dbf";set newname for datafile 2 to "/u01/app/oracle/oradata/oravn/undotbs01.dbf";set newname for datafile 3 to "/u01/app/oracle/oradata/oravn/sysaux01.dbf";set newname for datafile 4 to "/u01/app/oracle/oradata/oravn/users01.dbf";set newname for datafile 5 to "/u01/app/oracle/oradata/oravn/example01.dbf";restorecheck readonlyclone database

}executing Memory Script

executing command: SET until clauseexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAME

Starting restore at 27-APR-09

channel oravn: starting datafile backupset restorechannel oravn: specifying datafile(s) to restore from backup setrestoring datafile 00001 to /u01/app/oracle/oradata/oravn/system01.dbfrestoring datafile 00002 to /u01/app/oracle/oradata/oravn/undotbs01.dbfrestoring datafile 00003 to /u01/app/oracle/oradata/oravn/sysaux01.dbfrestoring datafile 00004 to /u01/app/oracle/oradata/oravn/users01.dbfrestoring datafile 00005 to /u01/app/oracle/oradata/oravn/example01.dbfchannel oravn: reading from backup piece /u01/oravn_backup/db_09kdgti0channel oravn: restored backup piece 1piece handle=/u01/oravn_backup/db_09kdgti0 tag=TAG20090427T100447channel oravn: restore complete, elapsed time: 00:00:46Finished restore at 27-APR-09sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ORAVN" RESETLOGS ARCHIVELOG MAXLOGFILES 16MAXLOGMEMBERS 3MAXDATAFILES 100MAXINSTANCES 8MAXLOGHISTORY 292LOGFILEGROUP 1 ( '/u01/app/oracle/oradata/oravn/redo01.log' ) SIZE 5 M REUSE,GROUP 2 ( '/u01/app/oracle/oradata/oravn/redo02.log' ) SIZE 5 M REUSE,GROUP 3 ( '/u01/app/oracle/oradata/oravn/redo03.log' ) SIZE 5 M REUSEDATAFILE

'/u01/app/oracle/oradata/oravn/system01.dbf'CHARACTER SET WE8ISO8859P1

contents of Memory Script:{switch clone datafile all;}executing Memory Script

datafile 2 switched to datafile copyinput datafile copy recid=1 stamp=685276005 filename=/u01/app/oracle/oradata/oravn/undotbs01.dbfdatafile 3 switched to datafile copyinput datafile copy recid=2 stamp=685276005 filename=/u01/app/oracle/oradata/oravn/sysaux01.dbfdatafile 4 switched to datafile copyinput datafile copy recid=3 stamp=685276005 filename=/u01/app/oracle/oradata/oravn/users01.dbfdatafile 5 switched to datafile copyinput datafile copy recid=4 stamp=685276005 filename=/u01/app/oracle/oradata/oravn/example01.dbf

contents of Memory Script:{set until scn 537681;recoverclone databasedelete archivelog

}executing Memory Script

executing command: SET until clause

Starting recover at 27-APR-09

starting media recovery

archive log thread 1 sequence 19 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_04_27/o1_mf_1_19_4zb887kf_.arcarchive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_04_27/o1_mf_1_19_4zb887kf_.arc thread=1 sequence=19media recovery complete, elapsed time: 00:00:02Finished recover at 27-APR-09

contents of Memory Script:{shutdown clone;startup clone nomount ;}executing Memory Script

database dismountedOracle instance shut down

connected to auxiliary database (not started)Oracle instance started

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytesVariable Size 92276304 bytesDatabase Buffers 188743680 bytesRedo Buffers 2973696 bytessql statement: CREATE CONTROLFILE REUSE SET DATABASE "ORAVN" RESETLOGS ARCHIVELOG MAXLOGFILES 16MAXLOGMEMBERS 3MAXDATAFILES 100MAXINSTANCES 8MAXLOGHISTORY 292LOGFILEGROUP 1 ( '/u01/app/oracle/oradata/oravn/redo01.log' ) SIZE 5 M REUSE,GROUP 2 ( '/u01/app/oracle/oradata/oravn/redo02.log' ) SIZE 5 M REUSE,GROUP 3 ( '/u01/app/oracle/oradata/oravn/redo03.log' ) SIZE 5 M REUSE

DATAFILE'/u01/app/oracle/oradata/oravn/system01.dbf'CHARACTER SET WE8ISO8859P1

contents of Memory Script:{set newname for tempfile 1 to "/u01/app/oracle/oradata/oravn/temp01.dbf";switch clone tempfile all;catalog clone datafilecopy "/u01/app/oracle/oradata/oravn/undotbs01.dbf";catalog clone datafilecopy "/u01/app/oracle/oradata/oravn/sysaux01.dbf";catalog clone datafilecopy "/u01/app/oracle/oradata/oravn/users01.dbf";catalog clone datafilecopy "/u01/app/oracle/oradata/oravn/example01.dbf";switch clone datafile all;}executing Memory Script

executing command: SET NEWNAME

renamed temporary file 1 to /u01/app/oracle/oradata/oravn/temp01.dbf in control file

cataloged datafile copydatafile copy filename=/u01/app/oracle/oradata/oravn/undotbs01.dbf recid=1 stamp=685276020

cataloged datafile copydatafile copy filename=/u01/app/oracle/oradata/oravn/sysaux01.dbf recid=2 stamp=685276020

cataloged datafile copydatafile copy filename=/u01/app/oracle/oradata/oravn/users01.dbf recid=3 stamp=685276021

cataloged datafile copydatafile copy filename=/u01/app/oracle/oradata/oravn/example01.dbf recid=4 stamp=685276021

datafile 2 switched to datafile copyinput datafile copy recid=1 stamp=685276020 filename=/u01/app/oracle/oradata/oravn/undotbs01.dbfdatafile 3 switched to datafile copyinput datafile copy recid=2 stamp=685276020 filename=/u01/app/oracle/oradata/oravn/sysaux01.dbfdatafile 4 switched to datafile copyinput datafile copy recid=3 stamp=685276021 filename=/u01/app/oracle/oradata/oravn/users01.dbfdatafile 5 switched to datafile copyinput datafile copy recid=4 stamp=685276021 filename=/u01/app/oracle/oradata/oravn/example01.dbf

contents of Memory Script:{Alter clone database open resetlogs;}executing Memory Script

database openedFinished Duplicate Db at 27-APR-09

RMAN> exit

Recovery Manager complete.[oracle@labs oravn_backup]$

- Giờ đã đến lúc xem lại kết quả Clone DB.

[oracle@labs oravn_backup]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Apr 27 10:27:57 2009



SQL> select name from v$database ;

NAME---------ORAVN

SQL> select count(*) from hr.employees ;

COUNT(*)----------107

SQL> exitDisconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning and Data Mining options[oracle@labs _backup]$

Như vậy chúng ta đã DUPLICATE được orcl sang DB mới là oravn.

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

WEL come to ORAVN !!

Thực hành Sử dụng RMAN để Duplicate Database

Documents

Transcript of Thực hành Sử dụng RMAN để Duplicate Database