oracle 11g duplicate from active database 複製資料庫(三)
下面介紹將資料庫複製到遠端主機相同目錄結構(且資料庫名與原資料庫名相同)的操作步驟
1.建立輔助例項的密碼檔案(這裡輔助例項名為db) ，在目的主機上為輔助例項建立密碼檔案可以有以下選項:
.手動建立密碼檔案，對於duplicate ... from active database有額外的要求。必須使用SYS使用者ID並且密碼必須與原資料庫的密碼相匹配。當想要使用單獨的密碼來建立密碼檔案時因此可以啟動輔助例項並使用它來連線原資料庫。
.在執行duplicate ... from active database命令時指定password file選項，在這種情況下，RMAN將原資料庫的密碼檔案複製到目的主機上並且覆蓋輔助例項已經存在的密碼檔案。如果原資料庫密碼檔案有多個密碼且你想讓它們在副本資料庫中使用時這種技術是非常有用的。
[oracle11@jingyong1 dbs]$ orapwd file=/u03/app/oracle/11.2.0/db/dbs/orapwdb password=system entries=10;
2.建立輔助例項網路連線,修改監聽檔案，使用靜態監聽來監聽輔助例項
[oracle11@jingyong1 admin]$ vi listener.ora
# listener.ora Network Configuration File: /u03/app/oracle/11.2.0/db/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = jingyong1)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = db)
(ORACLE_HOME =/u03/app/oracle/11.2.0/db)
)
)
[oracle11@jingyong1 admin]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 05-JUN-2015 11:25:35
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Starting /u03/app/oracle/11.2.0/db/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u03/app/oracle/11.2.0/db/network/admin/listener.ora
Log messages written to /u03/app/oracle/diag/tnslsnr/jingyong1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jingyong1)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=jingyong1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 05-JUN-2015 11:25:35
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u03/app/oracle/11.2.0/db/network/admin/listener.ora
Listener Log File /u03/app/oracle/diag/tnslsnr/jingyong1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jingyong1)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "cs" has 1 instance(s).
Instance "cs", status UNKNOWN, has 1 handler(s) for this service...
Service "db" has 1 instance(s).
Instance "db", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
給輔助例項增加網路服務名
[oracle11@jingyong1 admin]$ vi tnsnames.ora
# tnsnames.ora Network Configuration File: /u03/app/oracle/11.2.0/db/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
db =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.11)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = db)
(UR = A)
)
)
3.建立輔助例項的引數檔案,初始化引數檔案的目錄和內容依賴於給複製檔案命名選擇的策略。可以選擇建議的技術來對原主機和目的主機使用相同的命名策略。因此對於Oracle RAC環境，對於原主機和目的主機可以使用相同的ORACLE_SID。在引數檔案中將db_name設定為任意值，db_name是唯一必須設定的引數。建立引數檔案有以下選項:
.為輔助例項來建立文字引數檔案,初始化引數檔案的儲存目錄是在主機上作業系統特定的預設目錄。例如，在Linux和UNIX中預設引數檔名是ORACLE_HOME/dbs/initORACLE_SID.ora，在Windows平臺上引數檔名是ORACLE_HOME\database\initORACLE_SID.ora
.在執行duplicate命令時指定spfile子句。duplicate ... spfile技術最簡單，因為在執行復制時RMAN自動將原資料庫的spfile檔案複製到輔助例項或從備份中還原spfile檔案。如果在輔助例項上存在spfile檔案，那麼RMAN就會覆蓋它。
[oracle11@jingyong1 dbs]$ vi initdb.ora
db_name=db
上面只設定了db_name引數，因為我們在複製時使用spfile選項來複制原資料庫的SPFILE引數檔案
4.啟動輔助例項,啟動SQL*Plus並使用sysdba許可權連線到輔助例項。將輔助例項啟動到nomount狀態（如果引數檔案在預設目錄中startup命令不需要pfile引數)。注意:確保輔助例項使用文字引數檔案來啟動而不是SPFILE引數檔案。不要建立控制檔案或試圖mount或open輔助例項。
[oracle11@jingyong1 dbs]$ export ORACLE_SID=db [oracle11@jingyong1 dbs]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 5 11:30:03 2015 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount ORACLE instance started. Total System Global Area 150654976 bytes Fixed Size 1363216 bytes Variable Size 96469744 bytes Database Buffers 50331648 bytes Redo Buffers 2490368 bytes
5.在目標主機(執行被複制資料庫的主機）配置輔助例項的網路服務名
[oracle11@oracle11g admin]$ vi tnsnames.ora
db =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.56.11)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME =db)
(UR=A)
)
)
[oracle11@oracle11g admin]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 5 11:35:55 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> conn sys/system@dest_db as sysdba
Connected.
6.載入或開啟目標資料庫,如果RMAN連線到原資料庫作為目標資料庫，那麼原資料庫為了執行復制必須設定為合適的狀態。如果原資料庫例項沒有mount或open，那麼將原資料庫mount或open。如果執行active database複製，那麼確保滿足下面額外的條件:
-如果原資料庫open,那麼必須啟用歸檔
-如果原資料庫沒有open,那麼資料庫不需要執行例項恢復
由於原資料庫啟用了歸檔所以可以將原資料庫啟動到open狀態
[oracle11@jingyong1 dbs]$ export ORACLE_SID=db [oracle11@jingyong1 dbs]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 5 11:36:03 2015 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount ORACLE instance started. Total System Global Area 150654976 bytes Fixed Size 1363216 bytes Variable Size 96469744 bytes Database Buffers 50331648 bytes Redo Buffers 2490368 bytes
7.使用duplicate命令來執行資料庫複製。因為我們這裡複製後資料庫名為dup與原資料庫名不相同，所以不用指定spfile子句從原資料庫複製SPFILE引數檔案。因為複製後的副本資料庫與原資料庫的檔案擁有相同檔名，所以在執行duplicate命令時要使用nofilenamecheck選項。如果目錄不同，在pfile里加這個2個引數進行轉換:db_file_name_convert,log_file_name_convert。如果指定password file選項那麼RMAN將會從原資料庫複製密碼檔案到目的主機上。RMAN會自動複製SPFILE引數檔案到目的主機，使用SPFILE檔案來啟動輔助例項，複製所有需要的資料庫檔案和歸檔重做日誌檔案到目的主機，並恢復資料庫。最終，RMAN將使用resetlogs選項來建立聯機重做日誌。
[oracle11@oracle11g ~]$ rman target sys/system@db catalog rman/rman@cs auxiliary sys/system@dest_db
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Jun 5 11:55:56 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DB (DBID=1644809111)
connected to recovery catalog database
connected to auxiliary database: DB (not mounted)
RMAN> duplicate target database to db from active database password file spfile nofilenamecheck;
Starting Duplicate Db at 2015-06-05 11:56:10
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/u03/app/oracle/11.2.0/db/dbs/orapwdb' auxiliary format
'/u03/app/oracle/11.2.0/db/dbs/orapwdb' targetfile
'/u03/app/oracle/11.2.0/db/dbs/spfiledb.ora' auxiliary format
'/u03/app/oracle/11.2.0/db/dbs/spfiledb.ora' ;
sql clone "alter system set spfile= ''/u03/app/oracle/11.2.0/db/dbs/spfiledb.ora''";
}
executing Memory Script
Starting backup at 2015-06-05 11:56:10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=33 device type=DISK
Finished backup at 2015-06-05 11:56:13
sql statement: alter system set spfile= ''/u03/app/oracle/11.2.0/db/dbs/spfiledb.ora''
contents of Memory Script:
{
sql clone "alter system set db_name =
''DB'' comment=
''duplicate'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set db_name = ''DB'' comment= ''duplicate'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 422670336 bytes
Fixed Size 1365068 bytes
Variable Size 318770100 bytes
Database Buffers 96468992 bytes
Redo Buffers 6066176 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''DB'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''DB'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
backup as copy current controlfile auxiliary format '/u03/app/oracle/oradata/db/control01.ctl';
restore clone controlfile to '/u03/app/oracle/fast_recovery_area/db/control02.ctl' from
'/u03/app/oracle/oradata/db/control01.ctl';
alter clone database mount;
}
executing Memory Script
sql statement: alter system set db_name = ''DB'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''DB'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 422670336 bytes
Fixed Size 1365068 bytes
Variable Size 318770100 bytes
Database Buffers 96468992 bytes
Redo Buffers 6066176 bytes
Starting backup at 2015-06-05 11:56:26
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/u03/app/oracle/11.2.0/db/dbs/snapcf_db.f tag=TAG20150605T115626 RECID=5 STAMP=881582188
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 2015-06-05 11:56:29
Starting restore at 2015-06-05 11:56:29
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 2015-06-05 11:56:31
database mounted
RMAN-05538: WARNING: implicitly using DB_FILE_NAME_CONVERT
contents of Memory Script:
{
set newname for datafile 1 to
"/u03/app/oracle/oradata/db/system01.dbf";
set newname for datafile 2 to
"/u03/app/oracle/oradata/db/sysaux01.dbf";
set newname for datafile 3 to
"/u03/app/oracle/oradata/db/undotbs01.dbf";
set newname for datafile 4 to
"/u03/app/oracle/oradata/db/users01.dbf";
set newname for datafile 5 to
"/u03/app/oracle/oradata/db/test01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u03/app/oracle/oradata/db/system01.dbf" datafile
2 auxiliary format
"/u03/app/oracle/oradata/db/sysaux01.dbf" datafile
3 auxiliary format
"/u03/app/oracle/oradata/db/undotbs01.dbf" datafile
4 auxiliary format
"/u03/app/oracle/oradata/db/users01.dbf" datafile
5 auxiliary format
"/u03/app/oracle/oradata/db/test01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 2015-06-05 11:56:36
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u03/app/oracle/oradata/db/system01.dbf
output file name=/u03/app/oracle/oradata/db/system01.dbf tag=TAG20150605T115636
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u03/app/oracle/oradata/db/sysaux01.dbf
output file name=/u03/app/oracle/oradata/db/sysaux01.dbf tag=TAG20150605T115636
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u03/app/oracle/oradata/db/undotbs01.dbf
output file name=/u03/app/oracle/oradata/db/undotbs01.dbf tag=TAG20150605T115636
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u03/app/oracle/oradata/db/test01.dbf
output file name=/u03/app/oracle/oradata/db/test01.dbf tag=TAG20150605T115636
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u03/app/oracle/oradata/db/users01.dbf
output file name=/u03/app/oracle/oradata/db/users01.dbf tag=TAG20150605T115636
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 2015-06-05 11:58:45
sql statement: alter system archive log current
contents of Memory Script:
{
backup as copy reuse
archivelog like "/u03/archivelog/1_20_881232587.dbf" auxiliary format
"/u03/archivelog/1_20_881232587.dbf" ;
catalog clone archivelog "/u03/archivelog/1_20_881232587.dbf";
switch clone datafile all;
}
executing Memory Script
Starting backup at 2015-06-05 11:58:47
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=20 RECID=39 STAMP=881582326
output file name=/u03/archivelog/1_20_881232587.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 2015-06-05 11:58:49
cataloged archived log
archived log file name=/u03/archivelog/1_20_881232587.dbf RECID=39 STAMP=881582332
datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=881582333 file name=/u03/app/oracle/oradata/db/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=881582333 file name=/u03/app/oracle/oradata/db/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=881582333 file name=/u03/app/oracle/oradata/db/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=881582333 file name=/u03/app/oracle/oradata/db/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=9 STAMP=881582333 file name=/u03/app/oracle/oradata/db/test01.dbf
contents of Memory Script:
{
set until scn 886070;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 2015-06-05 11:58:50
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 20 is already on disk as file /u03/archivelog/1_20_881232587.dbf
archived log file name=/u03/archivelog/1_20_881232587.dbf thread=1 sequence=20
media recovery complete, elapsed time: 00:00:01
Finished recover at 2015-06-05 11:58:53
Oracle instance started
Total System Global Area 422670336 bytes
Fixed Size 1365068 bytes
Variable Size 318770100 bytes
Database Buffers 96468992 bytes
Redo Buffers 6066176 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''DB'' comment=
''Reset to original value by RMAN'' scope=spfile";
sql clone "alter system reset db_unique_name scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set db_name = ''DB'' comment= ''Reset to original value by RMAN'' scope=spfile
sql statement: alter system reset db_unique_name scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 422670336 bytes
Fixed Size 1365068 bytes
Variable Size 318770100 bytes
Database Buffers 96468992 bytes
Redo Buffers 6066176 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 SIZE 50 M ,
GROUP 2 SIZE 50 M ,
GROUP 3 SIZE 50 M
DATAFILE
'/u03/app/oracle/oradata/db/system01.dbf'
CHARACTER SET ZHS16GBK
contents of Memory Script:
{
set newname for tempfile 1 to
"/u03/app/oracle/oradata/db/temp01.dbf";
switch clone tempfile all;
catalog clone datafilecopy "/u03/app/oracle/oradata/db/sysaux01.dbf",
"/u03/app/oracle/oradata/db/undotbs01.dbf",
"/u03/app/oracle/oradata/db/users01.dbf",
"/u03/app/oracle/oradata/db/test01.dbf";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u03/app/oracle/oradata/db/temp01.dbf in control file
cataloged datafile copy
datafile copy file name=/u03/app/oracle/oradata/db/sysaux01.dbf RECID=1 STAMP=881582350
cataloged datafile copy
datafile copy file name=/u03/app/oracle/oradata/db/undotbs01.dbf RECID=2 STAMP=881582350
cataloged datafile copy
datafile copy file name=/u03/app/oracle/oradata/db/users01.dbf RECID=3 STAMP=881582350
cataloged datafile copy
datafile copy file name=/u03/app/oracle/oradata/db/test01.dbf RECID=4 STAMP=881582350
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=881582350 file name=/u03/app/oracle/oradata/db/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=881582350 file name=/u03/app/oracle/oradata/db/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=881582350 file name=/u03/app/oracle/oradata/db/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=881582350 file name=/u03/app/oracle/oradata/db/test01.dbf
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 2015-06-05 11:59:32
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28389881/viewspace-1718684/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 使用RMAN複製資料庫 active database資料庫Database
- Oracle 11GR2 Duplicate from BackupOracle
- Oracle 如何快速的 duplicate 一個資料庫Oracle資料庫
- ORACLE資料庫檢視ACQ（ACTIVE CHECKPOINT QUEUE）資訊Oracle資料庫
- oracle sqldeveloper選擇性複製備份資料庫OracleSQLDeveloper資料庫
- 資料庫複製（一）–複製介紹資料庫
- Converting Oracle Database from Linux to Windows using RMANOracleDatabaseLinuxWindows
- Oracle database 升級（文件）to 10.2.0.4 from 10.2.0.1OracleDatabase
- DM7資料複製之資料庫級複製資料庫
- 11G oracle資料庫重新啟動crsOracle資料庫
- 靜默安裝Oracle資料庫11gOracle資料庫
- Oracle 11g用impdp還原資料庫Oracle資料庫
- Oracle跨主機複製資料庫背後的意義Oracle資料庫
- 資料庫主從複製資料庫
- Oracle 11G資料庫單例項安裝Oracle資料庫單例
- 【OCP最新題庫解析(052)--題9】You want to install Oracle 11g databaseOracleDatabase
- rman duplicate建立異地auxiliary Database oracle_11g oracle_sid不同UXDatabaseOracle
- rman duplicate建立異地auxiliary Database oracle_11g oracle_sid相同UXDatabaseOracle
- oracle 11g 單例項資料庫的安裝Oracle單例資料庫
- Automatic Diagnostic Repository (ADR) in Oracle Database 11g Release 1 (ADRCI)OracleDatabase
- Mysql（Mariadb）資料庫主從複製MySql資料庫
- dimitri/pgcopydb：Postgres資料庫複製工具MITGC資料庫
- database資料庫的資料結構Database資料庫資料結構
- oracle 11g資料庫三大最佳化手段之一的ash生成及詳解Oracle資料庫
- mysql資料庫的主從複製和主主複製實踐MySql資料庫
- mysql資料庫實現主從複製MySql資料庫
- 分散式資料庫的複製原理 - Quastor分散式資料庫AST
- 架構設計（二）：資料庫複製架構資料庫
- mybatis oracle資料庫批次插入資料，忽略主鍵重複MyBatisOracle資料庫
- OGG_mysql 12c複製到OGG_oracle 11g部署方案MySqlOracle
- Oracle Data Pump 11G 資料泵元件Oracle元件
- Oracle 11g刪除庫重建Oracle
- Oracle DataBase Recovery資料庫dbf中了勒索病毒[Ransomwaree2020@cock.li].ekingOracleDatabase資料庫
- Oracle database 19c中獲取當前資料庫版本的方法OracleDatabase資料庫
- 資料共享（淺複製）與資料獨立（深複製）
- 利用SQL Server Management Studio(SSMS)複製資料庫SQLServerSSM資料庫
- TiDB 異構資料庫複製最佳實踐TiDB資料庫
- 異構資料庫間批量表快速複製資料庫
- GoldenGate異種資料庫之間的複製Go資料庫