oracle實驗-資料庫複製
資料庫複製【jdhq_db ===> clone_db】
一、克隆資料庫準備
在克隆前需要先複製引數檔案修改，並啟動到nomount狀態。在WINDOWS下面還需要建立對應的服務及配置監聽檔案。tnsnames.ora檔案
①．建立新的目錄結構
E:\demo\Clone_db\Archive
E:\demo\Clone_db\bdump
E:\demo\Clone_db\cdump
E:\demo\Clone_db\CtlFile
E:\demo\Clone_db\Dbfile
E:\demo\Clone_db\LogFile
E:\demo\Clone_db\pfile
E:\demo\Clone_db\udump
②．複製啟動引數檔案，並修改
#複製引數檔案：
c:\>Copy E:\demo\JDHQ_DB\pfile\initJDHQDB.ora E:\demo\Clone_db\pfile\initClone.ora
#修改引數檔案【E:\demo\Clone_db\pfile\initClone.ora】
instance_name=CLONEDB
db_name=CLONE_DB
control_files=("e:\demo\Clone_db\CtlFile\control01.ctl", "e:\demo\Clone_db\CtlFile\control02.ctl", "e:\demo\Clone_db\CtlFile\control03.ctl")
background_dump_dest=e:\demo\Clone_DB\bdump
core_dump_dest=e:\demo\Clone_DB\cdump
user_dump_dest=e:\demo\Clone_DB\udump
log_archive_dest_1 = "location=E:\demo\Clone_DB\Archive"
#使用如下此引數檔案，則不必set newname for datafile 檔案號 to 'E:\demo\Clone_DB\DBFILE\檔名'的轉換
#注：如果開始時不用DB_FILE_NAME_CONVERT和LOG_FILE_NAME_CONVERT引數，則需要修改set newname，
#由於資料檔案較多，如果手寫set newname指令碼會不切實際，可以用以下方法獲取set newname
DB_FILE_NAME_CONVERT =('E:\demo\JDHQ_DB\DBFILE' ,'E:\demo\Clone_DB\DBFILE')
LOG_FILE_NAME_CONVERT=('E:\demo\JDHQ_DB\LogFile','E:\demo\Clone_DB\LogFile')
③．使用oradim建立新的instance
c:\>oradim -new -sid CLONE -intpwd future -maxusers 5 -startmode a -pfile E:\demo\Clone_db\pfile\initClone.ora
例程已建立。
④．使用orapwd建立password檔案
c:\>orapwd file=D:\oracle\ora92\database\PWDCLONE.ora password=sys entries=10
增加啟動配置檔案
D:\oracle\ora92\database\initClone.ora
內容為：
IFILE='E:\demo\Clone_db\pfile\initClone.ora'
⑤．配置好監聽和tnsnames，然後使用sqlplus測試連線
# Added to the listener.ora SID_LIST
(SID_DESC =
(GLOBAL_DBNAME = CLONE_DB)
(ORACLE_HOME = d:\oracle\ora92)
(SID_NAME = CLONE)
)
# Added to the tnsnames.ora
CLONE_DB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = future-mget)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = CLONE_DB)
)
)
#重新啟動偵聽
net stop OracleOraHome92TNSListener
net start OracleOraHome92TNSListener
⑥．連線測試資料庫
c:\>set oracle_sid=CLONE
c:\>sqlplus /nolog
SQL*Plus: Release 10.1.0.2.0 - Production on 星期日 3月 27 21:01:13 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
SQL> conn sys/future as sysdba
SQL> STARTUP pfile=E:\demo\Clone_db\pfile\initClone.ora nomount;
二、備份目標資料庫
C:\>RMAN
connect rcvcat rman/rman@rman_db
connect target sys/change_on_install@jdhq_db
run {
allocate channel oem_backup_disk1 type disk ;
backup tag 'dbfull' format 'D:\dbfull_%d_%s_%p.bck'
database include current controlfile;
backup tag 'logfull' format 'D:\logfull_%d_%s_%p.bck' archivelog all;
release channel oem_backup_disk1;}
三、克隆資料庫
C:\>RMAN
=========================================================================
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
RMAN> connect target sys/change_on_install@jdhq_db
=========================================================================
connected to target database: JDHQ_DB (DBID=3736538461)
RMAN> connect catalog rman/rman@rman_db
=========================================================================
connectedto recovery catalog database
RMAN> connect auxiliary sys/future@clone_db
=========================================================================
connected to auxiliary database: CLONE_DB (not mounted)
RMAN> duplicate target database to clone_db;
=========================================================================
Starting Duplicate Db at 31-MAY-06
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=11 devtype=DISK
printing stored script. Memory Script
{
set until scn 6907023745;
set newname for datafile 1 to
"E:\DEMO\CLONE_DB\DBFILE\SYSJDHQDB.ODB";
set newname for datafile 2 to
"E:\DEMO\CLONE_DB\DBFILE\RBSGJDHQDB.ODB";
set newname for datafile 3 to
"E:\DEMO\CLONE_DB\DBFILE\DATAJDHQDB.ODB";
set newname for datafile 4 to
"E:\DEMO\CLONE_DB\DBFILE\INDXJDHQDB.ODB";
set newname for datafile 5 to
"E:\DEMO\CLONE_DB\DBFILE\TOOLJDHQDB.ODB";
restore
check readonly
clone database
;
}
executing script. Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 31-MAY-06
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 E:\DEMO\CLONE_DB\DBFILE\SYSJDHQDB.ODB
restoring datafile 00002 to E:\DEMO\CLONE_DB\DBFILE\RBSGJDHQDB.ODB
restoring datafile 00003 to E:\DEMO\CLONE_DB\DBFILE\DATAJDHQDB.ODB
restoring datafile 00004 to E:\DEMO\CLONE_DB\DBFILE\INDXJDHQDB.ODB
restoring datafile 00005 to E:\DEMO\CLONE_DB\DBFILE\TOOLJDHQDB.ODB
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=D:\DBFULL_JDHQ_DB_14_1.BCK tag=DBFULL params=NULL
channel ORA_AUX_DISK_1: restore complete
Finished restore at 31-MAY-06
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "clone_db" RESETLOGS ARCHIV
ELOG
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 ( 'E:\DEMO\CLONE_DB\LOGFILE\REDO01.LOG' ) SIZE 104857600 REUSE,
GROUP 2 ( 'E:\DEMO\CLONE_DB\LOGFILE\REDO02.LOG' ) SIZE 104857600 REUSE,
GROUP 3 ( 'E:\DEMO\CLONE_DB\LOGFILE\REDO03.LOG' ) SIZE 104857600 REUSE
DATAFILE
'E:\DEMO\CLONE_DB\DBFILE\SYSJDHQDB.ODB'
CHARACTER SET WE8ISO8859P1printing stored script. Memory Script
{
switch clone datafile all;
}
executing script. Memory Script
datafile 2 switched to datafile copy
input datafilecopy recid=1 stamp=591886336 filename=E:\DEMO\CLONE_DB\DBFILE\RBSG
JDHQDB.ODB
datafile 3 switched to datafile copy
input datafi lecopy recid=2 stamp=591886336 filename=E:\DEMO\CLONE_DB\DBFILE\DATA
JDHQDB.ODB
datafile 4 switched to datafile copy
input datafilecopy recid=3 stamp=591886336 filename=E:\DEMO\CLONE_DB\DBFILE\INDX
JDHQDB.ODB
datafile 5 switched to datafile copy
input datafilecopy recid=4 stamp=591886336 filename=E:\DEMO\CLONE_DB\DBFILE\TOOL
JDHQDB.ODB
printing stored script. Memory Script
{
set until scn 6907023745;
recover
clone database
delete archivelog
;
}
executing script. Memory Script
executing command: SET until clause
Starting recover at 31-MAY-06
using channelORA_AUX_DISK_1
starting media recovery
archive log thread 1 sequence 14 is already on disk as file E:\DEMO\JDHQ_DB\ARCH
IVE\JDHQDBT001S00014.ARC
archive log filename=E:\DEMO\JDHQ_DB\ARCHIVE\JDHQDBT001S00014.ARC thread=1 seque
nce=14
media recovery complete
Finished recover at 31-MAY-06
printing stored script. Memory Script
{
shutdown clone;
startup clone nomount ;
}
executing script. Memory Script
database dismounted
Oracle instance shut down
RMAN>
四、去掉init.ora的DB_FILE_NAME_CONVERT和LOG_FILE_NAME_CONVERT引數重新啟動資料庫
c:\>sqlplus /nolog
SQL> startup pfile=E:\demo\Clone_db\pfile\initClone.ora
一、克隆資料庫準備
在克隆前需要先複製引數檔案修改，並啟動到nomount狀態。在WINDOWS下面還需要建立對應的服務及配置監聽檔案。tnsnames.ora檔案
①．建立新的目錄結構
E:\demo\Clone_db\Archive
E:\demo\Clone_db\bdump
E:\demo\Clone_db\cdump
E:\demo\Clone_db\CtlFile
E:\demo\Clone_db\Dbfile
E:\demo\Clone_db\LogFile
E:\demo\Clone_db\pfile
E:\demo\Clone_db\udump
②．複製啟動引數檔案，並修改
#複製引數檔案：
c:\>Copy E:\demo\JDHQ_DB\pfile\initJDHQDB.ora E:\demo\Clone_db\pfile\initClone.ora
#修改引數檔案【E:\demo\Clone_db\pfile\initClone.ora】
instance_name=CLONEDB
db_name=CLONE_DB
control_files=("e:\demo\Clone_db\CtlFile\control01.ctl", "e:\demo\Clone_db\CtlFile\control02.ctl", "e:\demo\Clone_db\CtlFile\control03.ctl")
background_dump_dest=e:\demo\Clone_DB\bdump
core_dump_dest=e:\demo\Clone_DB\cdump
user_dump_dest=e:\demo\Clone_DB\udump
log_archive_dest_1 = "location=E:\demo\Clone_DB\Archive"
#使用如下此引數檔案，則不必set newname for datafile 檔案號 to 'E:\demo\Clone_DB\DBFILE\檔名'的轉換
#注：如果開始時不用DB_FILE_NAME_CONVERT和LOG_FILE_NAME_CONVERT引數，則需要修改set newname，
#由於資料檔案較多，如果手寫set newname指令碼會不切實際，可以用以下方法獲取set newname
DB_FILE_NAME_CONVERT =('E:\demo\JDHQ_DB\DBFILE' ,'E:\demo\Clone_DB\DBFILE')
LOG_FILE_NAME_CONVERT=('E:\demo\JDHQ_DB\LogFile','E:\demo\Clone_DB\LogFile')
③．使用oradim建立新的instance
c:\>oradim -new -sid CLONE -intpwd future -maxusers 5 -startmode a -pfile E:\demo\Clone_db\pfile\initClone.ora
例程已建立。
④．使用orapwd建立password檔案
c:\>orapwd file=D:\oracle\ora92\database\PWDCLONE.ora password=sys entries=10
增加啟動配置檔案
D:\oracle\ora92\database\initClone.ora
內容為：
IFILE='E:\demo\Clone_db\pfile\initClone.ora'
⑤．配置好監聽和tnsnames，然後使用sqlplus測試連線
# Added to the listener.ora SID_LIST
(SID_DESC =
(GLOBAL_DBNAME = CLONE_DB)
(ORACLE_HOME = d:\oracle\ora92)
(SID_NAME = CLONE)
)
# Added to the tnsnames.ora
CLONE_DB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = future-mget)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = CLONE_DB)
)
)
#重新啟動偵聽
net stop OracleOraHome92TNSListener
net start OracleOraHome92TNSListener
⑥．連線測試資料庫
c:\>set oracle_sid=CLONE
c:\>sqlplus /nolog
SQL*Plus: Release 10.1.0.2.0 - Production on 星期日 3月 27 21:01:13 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
SQL> conn sys/future as sysdba
SQL> STARTUP pfile=E:\demo\Clone_db\pfile\initClone.ora nomount;
二、備份目標資料庫
C:\>RMAN
connect rcvcat rman/rman@rman_db
connect target sys/change_on_install@jdhq_db
run {
allocate channel oem_backup_disk1 type disk ;
backup tag 'dbfull' format 'D:\dbfull_%d_%s_%p.bck'
database include current controlfile;
backup tag 'logfull' format 'D:\logfull_%d_%s_%p.bck' archivelog all;
release channel oem_backup_disk1;}
三、克隆資料庫
C:\>RMAN
=========================================================================
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
RMAN> connect target sys/change_on_install@jdhq_db
=========================================================================
connected to target database: JDHQ_DB (DBID=3736538461)
RMAN> connect catalog rman/rman@rman_db
=========================================================================
connectedto recovery catalog database
RMAN> connect auxiliary sys/future@clone_db
=========================================================================
connected to auxiliary database: CLONE_DB (not mounted)
RMAN> duplicate target database to clone_db;
=========================================================================
Starting Duplicate Db at 31-MAY-06
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=11 devtype=DISK
printing stored script. Memory Script
{
set until scn 6907023745;
set newname for datafile 1 to
"E:\DEMO\CLONE_DB\DBFILE\SYSJDHQDB.ODB";
set newname for datafile 2 to
"E:\DEMO\CLONE_DB\DBFILE\RBSGJDHQDB.ODB";
set newname for datafile 3 to
"E:\DEMO\CLONE_DB\DBFILE\DATAJDHQDB.ODB";
set newname for datafile 4 to
"E:\DEMO\CLONE_DB\DBFILE\INDXJDHQDB.ODB";
set newname for datafile 5 to
"E:\DEMO\CLONE_DB\DBFILE\TOOLJDHQDB.ODB";
restore
check readonly
clone database
;
}
executing script. Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 31-MAY-06
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 E:\DEMO\CLONE_DB\DBFILE\SYSJDHQDB.ODB
restoring datafile 00002 to E:\DEMO\CLONE_DB\DBFILE\RBSGJDHQDB.ODB
restoring datafile 00003 to E:\DEMO\CLONE_DB\DBFILE\DATAJDHQDB.ODB
restoring datafile 00004 to E:\DEMO\CLONE_DB\DBFILE\INDXJDHQDB.ODB
restoring datafile 00005 to E:\DEMO\CLONE_DB\DBFILE\TOOLJDHQDB.ODB
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=D:\DBFULL_JDHQ_DB_14_1.BCK tag=DBFULL params=NULL
channel ORA_AUX_DISK_1: restore complete
Finished restore at 31-MAY-06
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "clone_db" RESETLOGS ARCHIV
ELOG
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 ( 'E:\DEMO\CLONE_DB\LOGFILE\REDO01.LOG' ) SIZE 104857600 REUSE,
GROUP 2 ( 'E:\DEMO\CLONE_DB\LOGFILE\REDO02.LOG' ) SIZE 104857600 REUSE,
GROUP 3 ( 'E:\DEMO\CLONE_DB\LOGFILE\REDO03.LOG' ) SIZE 104857600 REUSE
DATAFILE
'E:\DEMO\CLONE_DB\DBFILE\SYSJDHQDB.ODB'
CHARACTER SET WE8ISO8859P1printing stored script. Memory Script
{
switch clone datafile all;
}
executing script. Memory Script
datafile 2 switched to datafile copy
input datafilecopy recid=1 stamp=591886336 filename=E:\DEMO\CLONE_DB\DBFILE\RBSG
JDHQDB.ODB
datafile 3 switched to datafile copy
input datafi lecopy recid=2 stamp=591886336 filename=E:\DEMO\CLONE_DB\DBFILE\DATA
JDHQDB.ODB
datafile 4 switched to datafile copy
input datafilecopy recid=3 stamp=591886336 filename=E:\DEMO\CLONE_DB\DBFILE\INDX
JDHQDB.ODB
datafile 5 switched to datafile copy
input datafilecopy recid=4 stamp=591886336 filename=E:\DEMO\CLONE_DB\DBFILE\TOOL
JDHQDB.ODB
printing stored script. Memory Script
{
set until scn 6907023745;
recover
clone database
delete archivelog
;
}
executing script. Memory Script
executing command: SET until clause
Starting recover at 31-MAY-06
using channelORA_AUX_DISK_1
starting media recovery
archive log thread 1 sequence 14 is already on disk as file E:\DEMO\JDHQ_DB\ARCH
IVE\JDHQDBT001S00014.ARC
archive log filename=E:\DEMO\JDHQ_DB\ARCHIVE\JDHQDBT001S00014.ARC thread=1 seque
nce=14
media recovery complete
Finished recover at 31-MAY-06
printing stored script. Memory Script
{
shutdown clone;
startup clone nomount ;
}
executing script. Memory Script
database dismounted
Oracle instance shut down
RMAN>
四、去掉init.ora的DB_FILE_NAME_CONVERT和LOG_FILE_NAME_CONVERT引數重新啟動資料庫
c:\>sqlplus /nolog
SQL> startup pfile=E:\demo\Clone_db\pfile\initClone.ora
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24558279/viewspace-746138/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle sqldeveloper選擇性複製備份資料庫OracleSQLDeveloper資料庫
- 資料庫複製（一）–複製介紹資料庫
- mysql資料庫實現主從複製MySql資料庫
- DM7資料複製之資料庫級複製資料庫
- mysql資料庫的主從複製和主主複製實踐MySql資料庫
- Oracle跨主機複製資料庫背後的意義Oracle資料庫
- 資料庫主從複製資料庫
- TiDB 異構資料庫複製最佳實踐TiDB資料庫
- Oracle實驗6--掌握Oracle資料庫的日誌操作Oracle資料庫
- MongoDB資料庫之主從複製配置實戰【轉】MongoDB資料庫
- 【Oracle 12c資料庫支援閃回庫功能】實驗Oracle資料庫
- linux下mysql主從複製，實現資料庫同步LinuxMySql資料庫
- Mysql（Mariadb）資料庫主從複製MySql資料庫
- 使用RMAN複製資料庫 active database資料庫Database
- dimitri/pgcopydb：Postgres資料庫複製工具MITGC資料庫
- 分散式資料庫的複製原理 - Quastor分散式資料庫AST
- 架構設計（二）：資料庫複製架構資料庫
- 資料庫實驗二資料庫
- mybatis oracle資料庫批次插入資料，忽略主鍵重複MyBatisOracle資料庫
- Oracle資料庫密碼延遲驗證Oracle資料庫密碼
- 資料庫實驗五：資料庫程式設計資料庫程式設計
- 資料庫實驗八 資料庫程式設計資料庫程式設計
- 資料共享（淺複製）與資料獨立（深複製）
- 利用SQL Server Management Studio(SSMS)複製資料庫SQLServerSSM資料庫
- 異構資料庫間批量表快速複製資料庫
- GoldenGate異種資料庫之間的複製Go資料庫
- Oracle EBS 資料庫密碼複雜度設定Oracle資料庫密碼複雜度
- Linux實現MySql資料庫的主從複製（一主一從）LinuxMySql資料庫
- 資料庫實驗五 資料庫的安全性資料庫
- Centos-Mysql複製備份還原資料庫CentOSMySql資料庫
- MySQL-主從複製之搭建從資料庫MySql資料庫
- MySQL-主從複製之搭建主資料庫MySql資料庫
- 什麼是單主資料庫複製？ -Vlad Mihalcea資料庫
- SQL Server實戰二：建立、修改、複製、刪除資料庫表並加以資料處理SQLServer資料庫
- Day 7.5 資料型別總結 + 複製 淺複製 深複製資料型別
- 檢視oracle資料庫真實大小Oracle資料庫
- 資料庫8530_實驗(1)資料庫
- 寫資料庫實驗報告資料庫
- Redis的資料複製Redis