oracle 复制 mysql_oracle复制数据库

准备两台设备，以我的为例一台source  ip:192.168.3.66和一台duplicate ip:192.168.3.77

1》基于备份集复制数据库，目录结构都一样(active database网络)

1.在duplicate端检查有没有亚参文件(如果没有的话就创建)

cd $ORACLE_HOME/dbs

touch dummy.ora

编辑vi dummy.ora添加

db_name='prod'

2.在source端复制一个口令文件(前提是在source有orapwprod口令文件，或者自己在duplicate自己创建一个)

scp $ORACLE_HOME/dbs/orapwprod 192.168.3.77:$ORACLE_HOME/dbs

3.检查duplicate必要的文件目录(如果没有创建)

mkdir -p /u01/admin/prod/adump

mkdir -p /u01/flash_recovery_area

mkdir -p /u01/arch/prod

mkdir -p /u01/oracle/dbs

mkdir -p /u01/oradata/prod

4.配置监听器source(可静态可动态)和duplicate(静态)：

两端的tns一样如下：

VBOX77 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.77)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = prod)

)

)

VBOX66 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.66)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = prod)

)

)

5.检查两端的连通性：

两边检查如下

sqlplus sys/oracle@vbox66 as sysdba

sqlplus sys/oracle@vbox77 as sysdba

6.在duplicate端重新登录sys用户sqlplus / as sysdba

SYS@prod>startup pfile=/u01/oracle/dbs/dummy.ora nomount

7.在source端

rman target sys/oracle@vbox66 auxiliary sys/oracle@vbox77

RMAN> duplicate target database to prod from active database spfile nofilenamecheck;

8.完成检验。

2》基于备份集复制数据库，目录结构不一样(active database网络)

1.在duplicate端检查有没有亚参文件(如果没有的话就创建)

cd $ORACLE_HOME/dbs

touch dummy.ora

编辑vi dummy.ora添加

db_name='auxprod'(这里我们换一个实例名)

注意：还要把.bash_profile中的ORACLE_SID实例名改成auxprod，然后生效。

2.在source端复制一个口令文件(前提是在source有orapwprod口令文件，或者自己在duplicate自己创建一个)

scp $ORACLE_HOME/dbs/orapwprod 192.168.3.77:$ORACLE_HOME/dbs

改口令名：mv orapwprod orapwauxprod

3.

因为是基于不同目录，所以在root下在新建一个目录/u02

mkdir /u02

chown -R oracle:oinstall /u02              ------如果这里不授权在后面会遇到权限问题

创建新目录：

mkdir -p /u02/arch1                             ----放归档

mkdir -p /u02/admin/auxprod/adump   -----审计文件

mkdir -p /u02/oradata/auxprod            ---放控制文件，数据文件等

4.配置监听器和tns(注意1》中与相同目录配置的区别)

duplicate端：

配置监听器 内容如下：

[oracle@wjm admin]$ vi listener.ora

# listener.ora Network Configuration File: /u01/oracle/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = wjm)(PORT = 1521))

)

)

ADR_BASE_LISTENER = /u01

SID_LIST_LISTENER =

(SID_DESC =

(GLOBAL_DBNAME= auxprod)

(ORACLE_HOME = /u01/oracle)

(SID_NAME = auxprod)

)

)

重启监听

lsnrctl stop

lsnrctl start

配置文件tnsnames.ora 内容如下：

[oracle@wjm admin]$ vi tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/oracle/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

vbox66 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.66)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = prod)

)

)

auxprod =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.77)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = auxprod)

)

)

5.检查两端的连通性：

两边检查如下

sqlplus sys/oracle@vbox66 as sysdba

sqlplus sys/oracle@vbox77 as sysdba

6.在duplicate端重新登录sys用户sqlplus / as sysdba

SYS@prod>startup pfile=/u01/oracle/dbs/dummy.ora nomount

7.在source端

DUPLICATE TARGET DATABASE

TO auxprod

FROM ACTIVE DATABASE

SPFILE

SET DB_NAME 'auxprod'

SET LOG_ARCHIVE_DEST_1='location=/u02/arch1'

SET CONTROL_FILES=

'/u02/oradata/auxprod/control01.ctl',

'/u02/oradata/auxprod/control02.ctl'

SET AUDIT_FILE_DEST='/u02/admin/auxprod/adump'

SET DB_FILE_NAME_CONVERT '/u01/oradata/prod','/u02/oradata/auxprod'

SET LOG_FILE_NAME_CONVERT '/u01/oradata/prod','/u02/oradata/auxprod'

NOFILENAMECHECK;

8.完成检验。

博主当时配置不同目录复制库遇到的错误：

1.在.bash_profile中ORACLE_SID没有改

2.口令文件命名错误

3.

ERROR:

ORA-09925: Unable to create audit trail file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 9925

ORA-01075: you are currently logged on

授权问题重新给/u01和/u02授权

chown -R oracle:oinstall /u01

chown -R oracle:oinstall /u02

注：在一个数据库上也可以复制方法同上。

3》在上面是基于备份集的复制，接下来是基于备份来复制库

基于备份来复制一个数据库

在原来的source设备上做一个全备+归档备份到/home/oracle/myrman

将source设备上的全备scp到另外一个设备duplicate上/home/oracle/rman

在source设备上select member from v$logfile;中的日志复制到另一个duplicate设备/home/oracle/rman

将duplicate中.bash_profile文件ORALCE_SID改为auxprod

在duplicate设备中打开rman target/

startup nomount;(提示缺少spfile文件)

--打开/u01/flash_recovery_area找到文件

restore spfile from '/home/oracle/rman/o1_mf_s_951945765_ds0no68m_.bkp';

startup force nomount;

连接SYS@auxprod>

show parameter controlfile

alter system set control_files='/u03/oradata/auxprod/control01.ctl','/u03/oradata/auxprod/control02.ctl','/u03/oradata/auxprod/control03.ctl' scope=spfile;

然后再rman中

restore controlfile from '/home/oracle/rman/o1_mf_s_951945765_ds0no68m_.bkp';

alter database mount;

catalog start with '/home/oracle/rman'

在source中批处理后执行

select 'set newname for datafile '||''''||' to '||''''||replace(name,'/u02/oradata/auxprod/','/u03/oradata/auxprod/')||''';' from v$datafile;

set newname for datafile '/u02/oradata/auxprod/' to '/u03/oradata/auxprod/system01.dbf';

set newname for datafile '/u02/oradata/auxprod/' to '/u03/oradata/auxprod/sysaux01.dbf';

set newname for datafile '/u02/oradata/auxprod/' to '/u03/oradata/auxprod/undotbs01.dbf';

set newname for datafile '/u02/oradata/auxprod/' to '/u03/oradata/auxprod/users01.dbf';

set newname for datafile '/u02/oradata/auxprod/' to '/u03/oradata/auxprod/example01.dbf';

alter database rename file '/u02/oradata/auxprod/redo03.log' to '/u03/oradata/auxprod/redo03.log'；

alter database rename file '/u02/oradata/auxprod/redo02.log' to '/u03/oradata/auxprod/redo02.log'；

alter database rename file '/u02/oradata/auxprod/redo01.log' to '/u03/oradata/auxprod/redo01.log'；

restore database;

recover database;

alter database open resetlogs;