RMAN Duplicate Database 的學習與測試_20100129

如何快速Clone一個生產系統作為測試環境.
1 複製生產庫的冷備,到另一臺機器上恢復.
分析:影響業務,要求停機

2 RMAN Duplicate Command.
分析: 不影響業務,保持生產系統線上.

還有其它方法嗎?

Duplicates a Database

To prepare for database duplication, you must first create an auxiliary

instance.
For the duplication to work, you must connect RMAN to both the target(primary)

database
and auxiliary instance started in NOMOUNT mode.

You must have at least one auxiliary channel allocated on the auxiliary

instance. The principal work of the duplication
is performed by the auxiliary channel, which starts a server session on the

duplicate host. This channel then restores the
necessary backups of the primary database, uses them to create the duplicate

database, and initiates recovery.

During duplication, RMAN must perform. incomplete recovery because the online

redo logs in the target are not backed up and cannot be applied to the duplicate

database. The farthest that RMAN can go in recovery of the duplicate database is

the most recent redo log archived by the target database.
(在Duplicating過程中,RMAN執行的是不完全恢復.不包括線上日誌.)

1 Preparing the Auxiliary Instance for Duplication: Basic Steps
Task 1: Create an Oracle Password File for the Auxiliary Instance
Task 2: Ensure Oracle Net Connectivity to the Auxiliary Instance
Task 3: Create an Initialization Parameter File for the Auxiliary Instance
Task 4: Start the Auxiliary Instance
Task 5: Mount or Open the Target Database
Task 6: Make Sure You Have the Necessary Backups and Archived Redo Logs
Task 7: DUPLICATE TARGET DATABASE TO duplicate;

2 Creating a Duplicate Database on a Local or Remote Host

Duplicate Test:

target database: 192.168.1.34 ORACLE_SID=mydb
ORACLE_HOME:/opt/oracle/product/9ir2

duplicate database: 192.168.1.29 ORACLE_SID=dupdb
ORACLE_HOME:/opt/oracle/product/9ir2

目標將192.168.1.34上的mydb透過
rman duplicate的方式Clone到 192.168.1.29上的dupdb上.
更改了資料庫名,資料檔案路徑,線上日誌路徑

2.1 備份 target database
In 192.168.1.34:
backup database plus archivelog;

並將所有的備份Piece複製到 dupdb 相同的目錄
/u01/backup/backup_MYDB_20100129_15_1
/u01/backup/backup_MYDB_20100129_16_1
/u01/backup/backup_MYDB_20100129_17_1
/opt/oracle/product/9ir2/dbs/c-2621354619-20100129-00;

除了物理複製的方式外,也可以透過NFS共享的形式載入Mount point.

2.2 在dupdb上建立密碼檔案
orapwd FILE=/opt/oracle/product/9ir2/dbs/orapwdupdb PASSWORD=sys ENTRIES=30

2.3 配置NET
In 192.168.1.29:
targetdb =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.34)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = mydb)
    )
)
lsnrctl start 開啟監聽

tnsping dupdb

TNS Ping Utility for Linux: Version 9.2.0.4.0 - Production on 29-JAN-2010

14:03:54

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL =

TCP)(HOST = 192.168.1.29)(PORT = 1521))) (CONNECT_DATA = (SID = mydb)))
OK (10 msec)

手工註冊監聽
SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /opt/oracle/product/9ir2)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = dupdb)
      (ORACLE_HOME = /opt/oracle/product/9ir2)
      (SID_NAME = dupdb)
    )
)

LISTENER =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.29)(PORT = 1521))
      )
    )
)

In 192.168.1.34:
dupdb =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.34)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = mydb)
    )
)

lsnrctl start 開啟監聽
tnsping targetdb

TNS Ping Utility for Linux: Version 9.2.0.4.0 - Production on 29-JAN-2010

13:14:17

Used parameter files:
/opt/oracle/product/9ir2/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL =

TCP)(HOST = 192.168.1.34)(PORT = 1521))) (CONNECT_DATA = (SID = mydb)))
OK (10 msec)

2.4 複製target的引數檔案到dupdb

更改
DB_NAME='dupdb'
CONTROL_FILES='/opt/oracle/oradata/dupdb/control01.ctl','/opt/oracle/oradata/dup

db/control03.ctl','/opt/oracle/oradata/dupdb/control03.ctl'
DB_FILE_NAME_CONVERT=(/opt/oracle/oradata/mydb/,/opt/oracle/oradata/dupdb/)
LOG_FILE_NAME_CONVERT=(/opt/oracle/oradata/mydb/,/opt/oracle/oradata/dupdb/)

並更改一些xxx_dest路徑:如background_dump_dest,如目錄不存在,則先建立目錄.

(在dupdb上)
更改後的pfile_20100129.ora
*.aq_tm_processes=1
*.background_dump_dest='/opt/oracle/admin/dupdb/bdump'
*.compatible='9.2.0.0.0'
*.core_dump_dest='/opt/oracle/admin/dupdb/cdump'
*.db_block_size=8192
*.db_cache_size=33554432
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='dupdb'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=mydbXDB)'
*.fast_start_mttr_target=300
*.hash_join_enabled=TRUE
*.instance_name='dupdb'
*.java_pool_size=117440512
*.job_queue_processes=10
*.large_pool_size=16777216
*.log_archive_dest='/opt/oracle/archive/'
*.log_archive_start=true
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=150
*.query_rewrite_enabled='FALSE'
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=117440512
*.sort_area_size=524288
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/opt/oracle/admin/dupdb/udump'
CONTROL_FILES='/opt/oracle/oradata/dupdb/control01.ctl','/opt/oracle/oradata/dup

db/control03.ctl','/opt/oracle/oradata/dupdb/control03.ctl'
DB_FILE_NAME_CONVERT=(/opt/oracle/oradata/mydb/,/opt/oracle/oradata/dupdb/)
LOG_FILE_NAME_CONVERT=(/opt/oracle/oradata/mydb/,/opt/oracle/oradata/dupdb/)

注意在引數檔案中必須設定至少兩個引數
DB_NAME='dupdb' (即使更改了instance_name='dupdb')
CONTROL_FILES='/opt/oracle/oradata/dupdb/control01.ctl','/opt/oracle/oradata/dup

db/control03.ctl','/opt/oracle/oradata/dupdb/control03.ctl'

4 Start the Auxiliary Instance
(在dupdb上)

export ORACLE_SID=dupdb
sqlplus /nolog
conn /as sysdba
create spfile from pfile='?/pfile_20100129.ora';
SQL> startup force nomount
ORACLE instance started.

Total System Global Area 320308744 bytes
Fixed Size                   742920 bytes
Variable Size             285212672 bytes
Database Buffers           33554432 bytes
Redo Buffers                 798720 bytes

show parametes xxx 確認更改是符合要求的

2.5 Duplicate target to dupdb
(在target上,實際上在兩端都可做)
> rman TARGET / AUXILIARY ;

Recovery Manager: Release 9.2.0.4.0 - 64bit Production

connected to target database: MYDB (DBID=2621354619)
connected to auxiliary database: dupdb (not mounted)

RMAN> DUPLICATE TARGET DATABASE TO dupdb;
(使用預設通道)

Starting Duplicate Db at 29-JAN-10
using target database controlfile instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=14 devtype=DISK

printing stored script. Memory Script
{
   set until scn 5481059;
   set newname for datafile 1 to
"/opt/oracle/oradata/dupdb/system01.dbf";
   set newname for datafile 2 to
"/opt/oracle/oradata/dupdb/undotbs01.dbf";
   set newname for datafile 3 to
"/opt/oracle/oradata/dupdb/cwmlite01.dbf";
   set newname for datafile 4 to
"/opt/oracle/oradata/dupdb/drsys01.dbf";
   set newname for datafile 5 to
"/opt/oracle/oradata/dupdb/example01.dbf";
   set newname for datafile 6 to
"/opt/oracle/oradata/dupdb/indx01.dbf";
   set newname for datafile 7 to
"/opt/oracle/oradata/dupdb/odm01.dbf";
   set newname for datafile 8 to
"/opt/oracle/oradata/dupdb/tools01.dbf";
   set newname for datafile 9 to
"/opt/oracle/oradata/dupdb/users01.dbf";
   set newname for datafile 10 to
"/opt/oracle/oradata/dupdb/xdb01.dbf";
   restore
   check readonly
   clone database
   ;
}
executing script. Memory Script

executing command: SET until clause

executing command: SET NEWNAME

Starting restore at 29-JAN-10

using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /opt/oracle/oradata/dupdb/system01.dbf
restoring datafile 00002 to /opt/oracle/oradata/dupdb/undotbs01.dbf
restoring datafile 00003 to /opt/oracle/oradata/dupdb/cwmlite01.dbf
restoring datafile 00004 to /opt/oracle/oradata/dupdb/drsys01.dbf
restoring datafile 00005 to /opt/oracle/oradata/dupdb/example01.dbf
restoring datafile 00006 to /opt/oracle/oradata/dupdb/indx01.dbf
restoring datafile 00007 to /opt/oracle/oradata/dupdb/odm01.dbf
restoring datafile 00008 to /opt/oracle/oradata/dupdb/tools01.dbf
restoring datafile 00009 to /opt/oracle/oradata/dupdb/users01.dbf
restoring datafile 00010 to /opt/oracle/oradata/dupdb/xdb01.dbf
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u01/backup/backup_MYDB_20100129_16_1 tag=TAG20100129T135654

params=NULL
channel ORA_AUX_DISK_1: restore complete
Finished restore at 29-JAN-10
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "dupdb" RESETLOGS

ARCHIVELOG
MAXLOGFILES     50
MAXLOGMEMBERS      5
MAXDATAFILES      100
MAXINSTANCES     1
MAXLOGHISTORY      226
LOGFILE
GROUP 1 ( '/opt/oracle/oradata/dupdb/redo01.log' ) SIZE 104857600 REUSE,
GROUP 2 ( '/opt/oracle/oradata/dupdb/redo02.log' ) SIZE 104857600 REUSE,
GROUP 3 ( '/opt/oracle/oradata/dupdb/redo03.log' ) SIZE 104857600 REUSE
DATAFILE
'/opt/oracle/oradata/dupdb/system01.dbf'
CHARACTER SET ZHS16GBK

printing stored script. Memory Script
{
switch clone datafile all;
}
executing script. Memory Script

datafile 2 switched to datafile copy
input datafilecopy recid=1 stamp=709573853

filename=/opt/oracle/oradata/dupdb/undotbs01.dbf
datafile 3 switched to datafile copy
input datafilecopy recid=2 stamp=709573853

filename=/opt/oracle/oradata/dupdb/cwmlite01.dbf
datafile 4 switched to datafile copy
input datafilecopy recid=3 stamp=709573853

filename=/opt/oracle/oradata/dupdb/drsys01.dbf
datafile 5 switched to datafile copy
input datafilecopy recid=4 stamp=709573853

filename=/opt/oracle/oradata/dupdb/example01.dbf
datafile 6 switched to datafile copy
input datafilecopy recid=5 stamp=709573853

filename=/opt/oracle/oradata/dupdb/indx01.dbf
datafile 7 switched to datafile copy
input datafilecopy recid=6 stamp=709573853

filename=/opt/oracle/oradata/dupdb/odm01.dbf
datafile 8 switched to datafile copy
input datafilecopy recid=7 stamp=709573853

filename=/opt/oracle/oradata/dupdb/tools01.dbf
datafile 9 switched to datafile copy
input datafilecopy recid=8 stamp=709573853

filename=/opt/oracle/oradata/dupdb/users01.dbf
datafile 10 switched to datafile copy
input datafilecopy recid=9 stamp=709573853

filename=/opt/oracle/oradata/dupdb/xdb01.dbf

printing stored script. Memory Script
{
   set until scn 5481059;
   recover
   clone database
    delete archivelog
   ;
}
executing script. Memory Script

executing command: SET until clause

Starting recover at 29-JAN-10
using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archive log restore to default destination
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=26
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u01/backup/backup_MYDB_20100129_17_1 tag=TAG20100129T135720

params=NULL
channel ORA_AUX_DISK_1: restore complete
archive log filename=/opt/oracle/archive/1_26.dbf thread=1 sequence=26
channel clone_default: deleting archive log(s)
archive log filename=/opt/oracle/archive/1_26.dbf recid=1 stamp=709573855
media recovery complete
Finished recover at 29-JAN-10

printing stored script. Memory Script
{
shutdown clone;
startup clone nomount ;
}
executing script. Memory Script

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 320308744 bytes

Fixed Size                      742920 bytes
Variable Size                285212672 bytes
Database Buffers              33554432 bytes
Redo Buffers                    798720 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "dupdb" RESETLOGS

ARCHIVELOG
MAXLOGFILES     50
MAXLOGMEMBERS      5
MAXDATAFILES      100
MAXINSTANCES     1
MAXLOGHISTORY      226
LOGFILE
GROUP 1 ( '/opt/oracle/oradata/dupdb/redo01.log' ) SIZE 104857600 REUSE,
GROUP 2 ( '/opt/oracle/oradata/dupdb/redo02.log' ) SIZE 104857600 REUSE,
GROUP 3 ( '/opt/oracle/oradata/dupdb/redo03.log' ) SIZE 104857600 REUSE
DATAFILE
'/opt/oracle/oradata/dupdb/system01.dbf'
CHARACTER SET ZHS16GBK

printing stored script. Memory Script
{
   catalog clone datafilecopy "/opt/oracle/oradata/dupdb/undotbs01.dbf";
   catalog clone datafilecopy "/opt/oracle/oradata/dupdb/cwmlite01.dbf";
   catalog clone datafilecopy "/opt/oracle/oradata/dupdb/drsys01.dbf";
   catalog clone datafilecopy "/opt/oracle/oradata/dupdb/example01.dbf";
   catalog clone datafilecopy "/opt/oracle/oradata/dupdb/indx01.dbf";
   catalog clone datafilecopy "/opt/oracle/oradata/dupdb/odm01.dbf";
   catalog clone datafilecopy "/opt/oracle/oradata/dupdb/tools01.dbf";
   catalog clone datafilecopy "/opt/oracle/oradata/dupdb/users01.dbf";
   catalog clone datafilecopy "/opt/oracle/oradata/dupdb/xdb01.dbf";
   switch clone datafile all;
}
executing script. Memory Script

cataloged datafile copy
datafile copy filename=/opt/oracle/oradata/dupdb/undotbs01.dbf recid=1

stamp=709575024

cataloged datafile copy
datafile copy filename=/opt/oracle/oradata/dupdb/cwmlite01.dbf recid=2

stamp=709575024

cataloged datafile copy
datafile copy filename=/opt/oracle/oradata/dupdb/drsys01.dbf recid=3

stamp=709575024

cataloged datafile copy
datafile copy filename=/opt/oracle/oradata/dupdb/example01.dbf recid=4

stamp=709575024

cataloged datafile copy
datafile copy filename=/opt/oracle/oradata/dupdb/indx01.dbf recid=5

stamp=709575024

cataloged datafile copy
datafile copy filename=/opt/oracle/oradata/dupdb/odm01.dbf recid=6

stamp=709575024

cataloged datafile copy
datafile copy filename=/opt/oracle/oradata/dupdb/tools01.dbf recid=7

stamp=709575024

cataloged datafile copy
datafile copy filename=/opt/oracle/oradata/dupdb/users01.dbf recid=8

stamp=709575024

cataloged datafile copy
datafile copy filename=/opt/oracle/oradata/dupdb/xdb01.dbf recid=9

stamp=709575024

datafile 2 switched to datafile copy
input datafilecopy recid=1 stamp=709575024

filename=/opt/oracle/oradata/dupdb/undotbs01.dbf
datafile 3 switched to datafile copy
input datafilecopy recid=2 stamp=709575024

filename=/opt/oracle/oradata/dupdb/cwmlite01.dbf
datafile 4 switched to datafile copy
input datafilecopy recid=3 stamp=709575024

filename=/opt/oracle/oradata/dupdb/drsys01.dbf
datafile 5 switched to datafile copy
input datafilecopy recid=4 stamp=709575024

filename=/opt/oracle/oradata/dupdb/example01.dbf
datafile 6 switched to datafile copy
input datafilecopy recid=5 stamp=709575024

filename=/opt/oracle/oradata/dupdb/indx01.dbf
datafile 7 switched to datafile copy
input datafilecopy recid=6 stamp=709575024

filename=/opt/oracle/oradata/dupdb/odm01.dbf
datafile 8 switched to datafile copy
input datafilecopy recid=7 stamp=709575024

filename=/opt/oracle/oradata/dupdb/tools01.dbf
datafile 9 switched to datafile copy
input datafilecopy recid=8 stamp=709575024

filename=/opt/oracle/oradata/dupdb/users01.dbf
datafile 10 switched to datafile copy
input datafilecopy recid=9 stamp=709575024

filename=/opt/oracle/oradata/dupdb/xdb01.dbf

printing stored script. Memory Script
{
Alter clone database open resetlogs;
}
executing script. Memory Script

database opened
Finished Duplicate Db at 29-JAN-10

注意如果發現 hang 住了

printing stored script. Memory Script
{
shutdown clone;(???)
startup clone nomount ;
}
executing script. Memory Script

而一直不動,
可能是你在dupdb上登入了,shutdown clone 在等待你的退出.

2.6 如何做到定時同步呢?
每天複製增量備份或歸檔檔案到dupdb. 每天執行DUPLICATE TARGET DATABASE TO dupdb;

RMAN Duplicate Database 的學習與測試_20100129

相關文章