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;
