當(dāng)前位置：首頁(yè) > 运维知识 > 数据库 >内容正文

数据库

ORACLE热备复制数据库全记录（可以修改数据库名和数据文件路径）

發(fā)布時(shí)間：2024/4/15 数据库 48 豆豆

生活随笔收集整理的這篇文章主要介紹了 ORACLE热备复制数据库全记录（可以修改数据库名和数据文件路径）小編覺得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.

oralce熱備是最簡(jiǎn)單，速度最快的數(shù)據(jù)庫(kù)復(fù)制方法，以下是我的全部記錄：
---------------------------------------------------------------------------------------------------------------------------------
計(jì)劃通過熱備的方式，把*.*.*.240上SGPMDB，復(fù)制到*.*.201.180上去,新庫(kù)的SID為SGTESTB

首先在180上用oracle創(chuàng)建一樣的目錄，用來存放240的全庫(kù)備份文件

創(chuàng)建一個(gè)密碼文件
orapwd FILE=/oracle/base/product/10.2.0/db_1/dbs/orapwdSGTESTB PASSWORD=oracle ENTRIES=30

準(zhǔn)備一個(gè)pfileSGTESTB.ora
DB_NAME=SGTESTB
db_block_size=8192
compatible=10.2.0.3.0
shared_pool_size=500M
db_file_name_convert=('/app/oracle/base/oradata/SGPMDB','/oracle/base/oradata/SGTESTB') ;
log_file_name_convert=('/app/oracle/base/admin/SGPMDB/arch','/oracle/base/admin/SGTESTB/arch') ;

在源庫(kù)上執(zhí)行
alter database begin backup;

select 'get '||substr(name,instr(name,'/',-1)+1),
'put '||name||' /oracle/base/oradata/SGTESTB/'||substr(name,instr(name,'/',-1)+1),bytes
from v$datafile;

--ftp數(shù)據(jù)文件
put /app/oracle/base/oradata/SGPMDB/system01.dbf /oracle/base/oradata/SGTESTB/system01.dbf
put /app/oracle/base/oradata/SGPMDB/undotbs01.dbf /oracle/base/oradata/SGTESTB/undotbs01.dbf
put /app/oracle/base/oradata/SGPMDB/sysaux01.dbf /oracle/base/oradata/SGTESTB/sysaux01.dbf
put /app/oracle/base/oradata/SGPMDB/users01.dbf /oracle/base/oradata/SGTESTB/users01.dbf
put /app/oracle/base/oradata/SGPMDB/DATA_A001.DBF /oracle/base/oradata/SGTESTB/DATA_A001.DBF
put /app/oracle/base/oradata/SGPMDB/DATA_C001.DBF /oracle/base/oradata/SGTESTB/DATA_C001.DBF
put /app/oracle/base/oradata/SGPMDB/DATA_D001.DBF /oracle/base/oradata/SGTESTB/DATA_D001.DBF
put /app/oracle/base/oradata/SGPMDB/DATA_M001.DBF /oracle/base/oradata/SGTESTB/DATA_M001.DBF
put /app/oracle/base/oradata/SGPMDB/DATA_R001.DBF /oracle/base/oradata/SGTESTB/DATA_R001.DBF
put /app/oracle/base/oradata/SGPMDB/DATA_O001.DBF /oracle/base/oradata/SGTESTB/DATA_O001.DBF
put /app/oracle/base/oradata/SGPMDB/DATA_E001.DBF /oracle/base/oradata/SGTESTB/DATA_E001.DBF
put /app/oracle/base/oradata/SGPMDB/DATA_G001.DBF /oracle/base/oradata/SGTESTB/DATA_G001.DBF
put /app/oracle/base/oradata/SGPMDB/DATA_P001.DBF /oracle/base/oradata/SGTESTB/DATA_P001.DBF
put /app/oracle/base/oradata/SGPMDB/DATA_S001.DBF /oracle/base/oradata/SGTESTB/DATA_S001.DBF
put /app/oracle/base/oradata/SGPMDB/IDX_A001.DBF /oracle/base/oradata/SGTESTB/IDX_A001.DBF
put /app/oracle/base/oradata/SGPMDB/IDX_C001.DBF /oracle/base/oradata/SGTESTB/IDX_C001.DBF
put /app/oracle/base/oradata/SGPMDB/IDX_D001.DBF /oracle/base/oradata/SGTESTB/IDX_D001.DBF
put /app/oracle/base/oradata/SGPMDB/IDX_M001.DBF /oracle/base/oradata/SGTESTB/IDX_M001.DBF
put /app/oracle/base/oradata/SGPMDB/IDX_R001.DBF /oracle/base/oradata/SGTESTB/IDX_R001.DBF
put /app/oracle/base/oradata/SGPMDB/IDX_O001.DBF /oracle/base/oradata/SGTESTB/IDX_O001.DBF
put /app/oracle/base/oradata/SGPMDB/IDX_E001.DBF /oracle/base/oradata/SGTESTB/IDX_E001.DBF
put /app/oracle/base/oradata/SGPMDB/IDX_G001.DBF /oracle/base/oradata/SGTESTB/IDX_G001.DBF
put /app/oracle/base/oradata/SGPMDB/IDX_P001.DBF /oracle/base/oradata/SGTESTB/IDX_P001.DBF
put /app/oracle/base/oradata/SGPMDB/IDX_S001.DBF /oracle/base/oradata/SGTESTB/IDX_S001.DBF
put /app/oracle/base/oradata/SGPMDB/DATA_MAN001.DBF /oracle/base/oradata/SGTESTB/DATA_MAN001.DBF
put /app/oracle/base/oradata/SGPMDB/IDX_MAN001.DBF /oracle/base/oradata/SGTESTB/IDX_MAN001.DBF
put /app/oracle/base/oradata/SGPMDB/DATA_SGPM.DBF /oracle/base/oradata/SGTESTB/DATA_SGPM.DBF
put /app/oracle/base/oradata/SGPMDB/DATA_Y.DBF /oracle/base/oradata/SGTESTB/DATA_Y.DBF
put /app/oracle/base/oradata/SGPMDB/IDX_Y.DBF /oracle/base/oradata/SGTESTB/IDX_Y.DBF
put /app/oracle/base/oradata/SGPMDB/DATA_OUT.DBF /oracle/base/oradata/SGTESTB/DATA_OUT.DBF
put /app/oracle/base/oradata/SGPMDB/IDX_OUT.DBF /oracle/base/oradata/SGTESTB/IDX_OUT.DBF
put /app/oracle/base/oradata/SGPMDB/DATA_ARC001.DBF /oracle/base/oradata/SGTESTB/DATA_ARC001.DBF
put /app/oracle/base/oradata/SGPMDB/IDX_ARC001.DBF /oracle/base/oradata/SGTESTB/IDX_ARC001.DBF
put /app/oracle/base/oradata/SGPMDB/pa_data01.dbf /oracle/base/oradata/SGTESTB/pa_data01.dbf
put /app/oracle/base/oradata/SGPMDB/pa_index01.dbf /oracle/base/oradata/SGTESTB/pa_index01.dbf

alter database end backup;

通過在sqlplus上執(zhí)行archive log list命令獲取當(dāng)前的日志順序號(hào)，
從oldest online log sequence開始到current log sequence的聯(lián)機(jī)redo日志應(yīng)該是熱備份的一部分。
SQL> archive log list ;
Database log mode????????????? Archive Mode
Automatic archival???????????? Enabled
Archive destination??????????? /app/oracle/base/admin/SGPMDB/arch
Oldest online log sequence???? 10131
Next log sequence to archive?? 10133
Current log sequence?????????? 10133

在sqlplus上執(zhí)行 alter system switch logfile;???? 命令來強(qiáng)迫日志切換，以便所有的日志都被歸檔。

使用alter database backup controlfile to trace;命令獲得控制文件的一個(gè)備份，
可以到%oracle_base%\admin\%oracle_sid%\udump目錄中尋找最新的跟蹤文件，其中有重建控制文件的全部命令。

alter database backup controlfile to trace resetlogs;

select value||'/'||
(select instance_name from v$instance)
||'_ora_'||spid||'.trc' from v$process,v$parameter where
name='user_dump_dest' and addr=(select paddr from v$session where sid=(select sid
from v$mystat where rownum=1));

獲取trace文件查看控制文件創(chuàng)建信息???? /app/oracle/base/admin/SGPMDB/udump/SGPMDB_ora_30797.trc

拷貝歸檔的日志文件到目標(biāo)庫(kù)相應(yīng)目錄。
select * from v$archived_log;
sgpmdb_10132_1_652109004.arch? sgpmdb_10133_1_652109004.arch

put /app/oracle/base/admin/SGPMDB/arch/sgpmdb_10132_1_652109004.arch /oracle/base/admin/SGTESTB/arch/sgpmdb_10132_1_652109004.arch
put /app/oracle/base/admin/SGPMDB/arch/sgpmdb_10133_1_652109004.arch /oracle/base/admin/SGTESTB/arch/sgpmdb_10133_1_652109004.arch

在目標(biāo)庫(kù)上啟動(dòng)輔助實(shí)例
export ORACLE_SID=SGTESTB
sqlplus / as sysdba
STARTUP NOMOUNT PFILE='/oracle/base/admin/SGTESTB/pfile/pfileSGTESTB.ora'

在控制文件備份中，有創(chuàng)建控制文件的腳本
CREATE CONTROLFILE set DATABASE "SGTESTB" RESETLOGS? ARCHIVELOG
??? MAXLOGFILES 16
??? MAXLOGMEMBERS 3
??? MAXDATAFILES 100
??? MAXINSTANCES 8
??? MAXLOGHISTORY 4672
LOGFILE
? GROUP 1 '/oracle/base/oradata/SGTESTB/redo01.log'? SIZE 50M,
? GROUP 2 '/oracle/base/oradata/SGTESTB/redo02.log'? SIZE 50M,
? GROUP 3 '/oracle/base/oradata/SGTESTB/redo03.log'? SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/oracle/base/oradata/SGTESTB/system01.dbf',
'/oracle/base/oradata/SGTESTB/undotbs01.dbf',
'/oracle/base/oradata/SGTESTB/sysaux01.dbf',
'/oracle/base/oradata/SGTESTB/users01.dbf',
'/oracle/base/oradata/SGTESTB/DATA_A001.DBF',
'/oracle/base/oradata/SGTESTB/DATA_C001.DBF',
'/oracle/base/oradata/SGTESTB/DATA_D001.DBF',
'/oracle/base/oradata/SGTESTB/DATA_M001.DBF',
'/oracle/base/oradata/SGTESTB/DATA_R001.DBF',
'/oracle/base/oradata/SGTESTB/DATA_O001.DBF',
'/oracle/base/oradata/SGTESTB/DATA_E001.DBF',
'/oracle/base/oradata/SGTESTB/DATA_G001.DBF',
'/oracle/base/oradata/SGTESTB/DATA_P001.DBF',
'/oracle/base/oradata/SGTESTB/DATA_S001.DBF',
'/oracle/base/oradata/SGTESTB/IDX_A001.DBF',
'/oracle/base/oradata/SGTESTB/IDX_C001.DBF',
'/oracle/base/oradata/SGTESTB/IDX_D001.DBF',
'/oracle/base/oradata/SGTESTB/IDX_M001.DBF',
'/oracle/base/oradata/SGTESTB/IDX_R001.DBF',
'/oracle/base/oradata/SGTESTB/IDX_O001.DBF',
'/oracle/base/oradata/SGTESTB/IDX_E001.DBF',
'/oracle/base/oradata/SGTESTB/IDX_G001.DBF',
'/oracle/base/oradata/SGTESTB/IDX_P001.DBF',
'/oracle/base/oradata/SGTESTB/IDX_S001.DBF',
'/oracle/base/oradata/SGTESTB/DATA_MAN001.DBF',
'/oracle/base/oradata/SGTESTB/IDX_MAN001.DBF',
'/oracle/base/oradata/SGTESTB/DATA_SGPM.DBF',
'/oracle/base/oradata/SGTESTB/DATA_Y.DBF',
'/oracle/base/oradata/SGTESTB/IDX_Y.DBF',
'/oracle/base/oradata/SGTESTB/DATA_OUT.DBF',
'/oracle/base/oradata/SGTESTB/IDX_OUT.DBF',
'/oracle/base/oradata/SGTESTB/DATA_ARC001.DBF',
'/oracle/base/oradata/SGTESTB/IDX_ARC001.DBF',
'/oracle/base/oradata/SGTESTB/pa_data01.dbf',
'/oracle/base/oradata/SGTESTB/pa_index01.dbf'
CHARACTER SET UTF8
;

在目標(biāo)機(jī)上指明歸檔日志的位置
SQL> alter system set log_archive_dest='/oracle/base/admin/SGTESTB/arch' ;

登錄RMAN，讓RMAN識(shí)別到歸檔日志
RMAN> catalog start with '/oracle/base/admin/SGTESTB/arch';

RMAN> recover database;
Starting recover at 17-AUG-09
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 10133 is already on disk as file /oracle/base/admin/SGTESTB/arch/sgpmdb_10133_1_652109004.arch
archive log filename=/oracle/base/admin/SGTESTB/arch/sgpmdb_10133_1_652109004.arch thread=1 sequence=10133
unable to find archive log
archive log thread=1 sequence=10134
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 08/17/2009 11:26:56
RMAN-06054: media recovery requesting unknown log: thread 1 seq 10134 lowscn 10110329200744

沒有更新的日志，到此就恢復(fù)完成，可以打開庫(kù)了

重設(shè)日志打開
RMAN> alter database open resetlogs;

增加臨時(shí)表空間
ALTER TABLESPACE TEMP ADD TEMPFILE '/oracle/base/oradata/SGTESTB/temp01.dbf'
???? SIZE 50M? REUSE AUTOEXTEND ON NEXT 655360? MAXSIZE 32767M;

復(fù)制控制文件，并且在pfile里面指定
然后重新根據(jù)pfile生成spfile，并且修改/etc/oratab
*.audit_file_dest='/oracle/base/admin/SGTESTB/adump'
*.background_dump_dest='/oracle/base/admin/SGTESTB/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/oracle/base/oradata/SGTESTB/control01.ctl','/oracle/base/oradata/SGTESTB/control02.ctl','/oracle/base/oradata/SGTESTB/control03.ctl'
*.core_dump_dest='/oracle/base/admin/SGTESTB/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='SGTESTB'
*.db_recovery_file_dest_size=2147483648
*.db_recovery_file_dest=''
*.log_archive_dest='/oracle/base/admin/SGTESTB/arch'
*.nls_language='SIMPLIFIED CHINESE'
*.processes=300
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle/base/admin/SGTESTB/udump'
*.sga_max_size=1048576000
*.sga_target=1048576000

STARTUP PFILE='/oracle/base/admin/SGTESTB/pfile/pfileSGTESTB.ora' ;

創(chuàng)建SPFILE：
CREATE SPFILE FROM PFILE='/oracle/base/admin/SGTESTB/pfile/pfileSGTESTB.ora';

轉(zhuǎn)載于:https://www.cnblogs.com/caibird2005/archive/2009/08/17/1548246.html

總結(jié)

以上是生活随笔為你收集整理的ORACLE热备复制数据库全记录（可以修改数据库名和数据文件路径）的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。

上一篇：关于前端的那点事儿
下一篇： flex 3名域namespace的使用