【Oracle】 Oracle使用hot backup克隆数据库

使用hot backup克隆数据库，不影响数据的运行。

但是要求源库必须是归档模式。源库和目标库版本要一致，需要先在目标服务器上安装好oracle软件。

 

?

1 2 3

源库：srcdb   目标库：trgdb

 

1.确保源库开启了归档

?

1	SQL> archive log list;

 

2.在目标服务器上创建相应的目录

?

1	mkdir -p /u03/oracle/oradata/TRGDBmkdir -p /archive/oradata/TRGDB/arch

　　

3.在源库端创建一个控制文件

通过trace实现

?

1	alter database backup controlfile to trace as '/export/home/oracle/ctrl_bkokp.sql';

　　

4.将源库至于hot backup模式

?

1 2 3 4 5 6 7

SQL>ALTER SYSTEM SWITCH LOGFILE; SQL> ALTER DATABASE BEGIN BACKUP; SQL> select distinct status from v$backup;   STATUS ------------------ ACTIVE

　　

5.拷贝数据文件，tempfiles和备份好的控制文件到目标库

?

1 2	scp /u03/oracle/oradata/SRCDB/*dbf oracle@targret-host.dbclass.com:/u03/oracle/oradata/TRGDB/ scp /export/home/oracle/ctrl_bkokp.sql oracle@targret-host.dbclass.com:/u03/oracle/oradata/TRGDB/

　　

6.拷贝结束后，取消源库的hot backup状态

?

1	ALTER DATABASE END BACKUP;ALTER SYSTEM ARCHIVE LOG CURRENT;

　　

7.为目标库创建initfile

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

cat initTRGDB.ora   *.audit_file_dest='/u01/app/oracle/admin/TRGDB/adump' *.audit_trail='D *.compatible='12.1.0.2.0' *.control_files='/u03/oracle/oradata/TRGDB/control01.ctl','/u03/oracle/oradata/TRGDB/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='TRGDB' *.diagnostic_dest='/u01/app/oracle/'*.event='' *.open_cursors=300 *.pga_aggregate_target=524288000 *.processes=1000 *.remote_login_passwordfile='EXCLUSIVE' *.sec_case_sensitive_logon=FALSE *.sessions=1536 *.sga_max_size=7373586432 *.sga_target=7373586432 *.undo_tablespace='UNDOTBS1'

　

8.启动目标库到nomount状态

?

1 2 3

export ORACLE_SID=TRGDB   startup nomount pfile=initTRGDB.ora

　　

9.目标库重新创建controlfile

在步骤3，我们已经将控制文件信息写到了trace文件

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

CREATE CONTROLFILE SET DATABASE "TRGDB" RESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 2336 LOGFILE GROUP 1 '/u01/oracle/oradata/TRGDB/redo01.log' SIZE 50M BLOCKSIZE 512, GROUP 2 '/u01/oracle/oradata/TRGDB/redo02.log' SIZE 50M BLOCKSIZE 512, GROUP 3 '/u01/oracle/oradata/TRGDB/redo03.log' SIZE 50M BLOCKSIZE 512 -- STANDBY LOGFILE DATAFILE '/u01/oracle/oradata/TRGDB/system01.dbf', '/u01/oracle/oradata/TRGDB/CTLDATA_01.dbf', '/u01/oracle/oradata/TRGDB/sysaux01.dbf', '/u01/oracle/oradata/TRGDB/undotbs01.dbf', '/u01/oracle/oradata/TRGDB/CTLIDX_01.dbf', '/u01/oracle/oradata/TRGDB/users01.dbf', '/u01/oracle/oradata/TRGDB/catalog01.dbf', '/u01/oracle/oradata/TRGDB/catalog_idx01.dbf', '/u01/oracle/oradata/TRGDB/GGATE_01.dbf' CHARACTER SET AL32UTF8 ;

　　

这里使用了set database，因为是在目标库上克隆一个新库。

如果想和源库一样，可以使用命令reuse database。

如果现在想以resetlog模式开启数据库，会报以下错误：

?

1 2 3 4 5 6

SQL>  alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01113: file 1 needs media recovery ORA-01110: data file 1: ‘/u01/oracle/oradata/TRGDB/system01.dbf’

　　

解决这个问题，需要执行recover database，应用hot backup期间生成的归档日志

?

1 2 3 4 5 6 7 8 9 10

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;     SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;     ORA-00279: change 11491098201897 generated at 05/17/2016 16:36:27 needed for thread 1 ORA-00289: suggestion : /archive/oradata/TRGDB/arch/1_5642_907410560.arc ------- > required archive ORA-00280: change 11491098201897 for thread 1 is in sequence #5642

　　

需要归档日志从1_5642_907410560.arc开始。

将这些归档日志拷贝到目标库

?

1 2	scp /archive/oradata/SRCDB/arch/1_5642_907410560.arc oracle@targret-host.dbclass.com:/archive/oradata/TRGDB/arch scp /archive/oradata/SRCDB/arch/1_5643_907410560.arc oracle@targret-host.dbclass.com:/archive/oradata/TRGDB/arch

　　

再次recover，输入auto

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL; ORA-00279: change 11491098201897 generated at 05/17/2016 16:36:27 needed for thread 1 ORA-00289: suggestion : /archive/oradata/TRGDB/arch/1_5642_907410560.arc ORA-00280: change 11491098201897 for thread 1 is in sequence #5642       Specify log: {=suggested | filename | AUTO | CANCEL} AUTO ORA-00279: change 11491098210561 generated at 05/17/2016 16:55:09 needed for thread 1 ORA-00289: suggestion : /archive/oradata/TRGDB/arch/1_5643_907410560.arc ORA-00280: change 11491098210561 for thread 1 is in sequence #5643 ORA-00278: log file '/archive/oradata/TRGDB/arch/1_5642_907410560.arc' no longer needed for this recovery

　

应用结束，就可以开启数据库了：

?

1 2 3

SQL> alter database open resetlogs;    Database altered.

　　

如果，打开数据库还是报错，比如数据文件需要恢复，表明还有归档日志没有应用到目标库。

​