一. 安装规划
IP | ROLE |
192.168.1.235 | 克隆对象 |
192.168.1.221 | 克隆库 |
二. 备库创建相应的dump文件夹
# 在主库查询对应的dump目录
select name, value from v$parameter where name in ('audit_file_dest', 'background_dump_dest', 'control_files', 'core_dump_dest', 'user_dump_dest', 'db_recovery_file_dest' ) ORDER BY name ASC;
# 备库用Oracle用户执行创建目录
mkdir -p $value
三.?创建实例密钥文件
- 主库生成密钥文件
orapwd FILE=/u01/app/oracle/dbs/orapwora235 password=li0924 entries=5;
- 将密钥文件传输到备库
scp /u01/app/oracle/dbs/orapwora235 oracle@192.168.1.221:/u01/app/oracle/dbs
四.?创建参数文件
- 主库通过spfile生成pfile文件
create pfile from spfile;
- 复制参数文件到Standby库
scp /u01/app/oracle/dbs/initora235.ora oracle@192.168.1.221:/u01/app/oracle/dbs
- 将Standby端启动到nomount状态
[oracle@oracle221 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Thu Aug 9 22:56:49 2018 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount pfile='/u01/app/oracle/dbs/initora235.ora'; ORACLE instance started. Total System Global Area 780824576 bytes Fixed Size 2257312 bytes Variable Size 511708768 bytes Database Buffers 264241152 bytes Redo Buffers 2617344 bytes
五. 配置主备库的监听文件和网络文件
设置Primary库和Standby库的service_name
SQL> show parameter service_name; NAME TYPE VALUE ------------------------------------ ----------- --------------- service_names string lottu SQL> alter system set service_names='db_primary' scope=both; System altered. SQL> show parameter service_name; NAME TYPE VALUE ------------------------------------ ----------- ---------------- service_names string db_primary
备库的service_name设置为db_standby;需要到参数文件修改。
*.service_names='db_standby'
Primary库配置listener.ora
LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.235)(PORT = 1521)(IP = FIRST))) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC)) ) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = ora235) (GLOBAL_DBNAME = db_primary) (ORACLE_HOME =/u01/app/oracle) ) )
Standby库配置listener.ora
(DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.221)(PORT = 1521)(IP = FIRST))) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC)) ) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = ora235) (GLOBAL_DBNAME = db_standby) (ORACLE_HOME =/u01/app/oracle) ) )
Primary库和Standby库设置tnsname.ora
tns_primary= (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.235)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = db_primary) ) (HS = OK) ) tns_standby= (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.221)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = db_standby) ) (HS = OK) )
六. 克隆Oracle
在Standby库执行:DUPLICATE TARGET DATABASE TO ORA235 FROM ACTIVE DATABASE SPFILE NOFILENAMECHECK;
[oracle@oracle221 ~]$ rlwrap? rman target?sys/li0924@tns_primary?auxiliary?sys/li0924@tns_standby Recovery Manager: Release 11.2.0.4.0 - Production on Thu Aug 16 23:21:27 2018 Copyright (c) 1982, 2011, Oracle and/or its affiliates.? All rights reserved. connected to target database: ORA235 (DBID=2047494122) connected to auxiliary database: ORA235 (not mounted) RMAN> DUPLICATE TARGET DATABASE TO ORA235 FROM ACTIVE DATABASE SPFILE NOFILENAMECHECK; Starting Duplicate Db at 16-AUG-18 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=19 device type=DISK contents of Memory Script: { backup as copy reuse targetfile? '/u01/app/oracle/dbs/spfileora235.ora' auxiliary format '/u01/app/oracle/dbs/spfileora235.ora'? ; sql clone "alter system set spfile= ''/u01/app/oracle/dbs/spfileora235.ora''"; } executing Memory Script Starting backup at 16-AUG-18 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=27 device type=DISK Finished backup at 16-AUG-18 sql statement: alter system set spfile= ''/u01/app/oracle/dbs/spfileora235.ora'' contents of Memory Script: { sql clone "alter system set? db_name = ''ORA235'' comment= ''duplicate'' scope=spfile"; sql clone "alter system set? db_unique_name = ''ORA235'' comment= ''duplicate'' scope=spfile"; shutdown clone immediate; startup clone nomount; } executing Memory Script sql statement: alter system set? db_name =? ''ORA235'' comment= ''duplicate'' scope=spfile sql statement: alter system set? db_unique_name =? ''ORA235'' comment= ''duplicate'' scope=spfile Oracle instance shut down connected to auxiliary database (not started) Oracle instance started Total System Global Area? ? 780824576 bytes Fixed Size? ? ? ? ? ? ? ? ? ? 2257312 bytes Variable Size? ? ? ? ? ? ? ? 511708768 bytes Database Buffers? ? ? ? ? ? 264241152 bytes Redo Buffers? ? ? ? ? ? ? ? ? 2617344 bytes contents of Memory Script: { sql clone "alter system set? db_name = ''ORA235'' comment= ''Modified by RMAN duplicate'' scope=spfile"; sql clone "alter system set? db_unique_name = ''ORA235'' comment= ''Modified by RMAN duplicate'' scope=spfile"; shutdown clone immediate; startup clone force nomount backup as copy current controlfile auxiliary format? '/u01/app/oradata/ora235/control01.ctl'; restore clone controlfile to? '/u01/app/oradata/ora235/control02.ctl' from '/u01/app/oradata/ora235/control01.ctl'; alter clone database mount; } executing Memory Script sql statement: alter system set? db_name =? ''ORA235'' comment= ''Modified by RMAN duplicate'' scope=spfile sql statement: alter system set? db_unique_name =? ''ORA235'' comment= ''Modified by RMAN duplicate'' scope=spfile Oracle instance shut down Oracle instance started Total System Global Area? ? 780824576 bytes Fixed Size? ? ? ? ? ? ? ? ? ? 2257312 bytes Variable Size? ? ? ? ? ? ? ? 511708768 bytes Database Buffers? ? ? ? ? ? 264241152 bytes Redo Buffers? ? ? ? ? ? ? ? ? 2617344 bytes Starting backup at 16-AUG-18 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy copying current control file output file name=/u01/app/oracle/dbs/snapcf_ora235.f tag=TAG20180816T232148 RECID=6 STAMP=984352909 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 Finished backup at 16-AUG-18 Starting restore at 16-AUG-18 allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=18 device type=DISK channel ORA_AUX_DISK_1: copied control file copy Finished restore at 16-AUG-18 database mounted RMAN-05538: WARNING: implicitly using DB_FILE_NAME_CONVERT contents of Memory Script: { sql clone 'alter database flashback off'; set newname for datafile? 1 to "/u01/app/oradata/ora235/system01.dbf"; set newname for datafile? 2 to "/u01/app/oradata/ora235/sysaux01.dbf"; set newname for datafile? 3 to "/u01/app/oradata/ora235/undotbs01.dbf"; set newname for datafile? 4 to "/u01/app/oradata/ora235/users01.dbf"; set newname for datafile? 5 to "/data/oracle/data/lottu01.dbf"; backup as copy reuse datafile? 1 auxiliary format "/u01/app/oradata/ora235/system01.dbf"? datafile 2 auxiliary format "/u01/app/oradata/ora235/sysaux01.dbf"? datafile 3 auxiliary format "/u01/app/oradata/ora235/undotbs01.dbf"? datafile 4 auxiliary format "/u01/app/oradata/ora235/users01.dbf"? datafile 5 auxiliary format "/data/oracle/data/lottu01.dbf"? ; sql 'alter system archive log current'; } executing Memory Script sql statement: alter database flashback off executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting backup at 16-AUG-18 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00005 name=/data/oracle/data/lottu01.dbf output file name=/data/oracle/data/lottu01.dbf tag=TAG20180816T232159 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:45 channel ORA_DISK_1: starting datafile copy input datafile file number=00001 name=/u01/app/oradata/ora235/system01.dbf output file name=/u01/app/oradata/ora235/system01.dbf tag=TAG20180816T232159 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45 channel ORA_DISK_1: starting datafile copy input datafile file number=00002 name=/u01/app/oradata/ora235/sysaux01.dbf output file name=/u01/app/oradata/ora235/sysaux01.dbf tag=TAG20180816T232159 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35 channel ORA_DISK_1: starting datafile copy input datafile file number=00003 name=/u01/app/oradata/ora235/undotbs01.dbf output file name=/u01/app/oradata/ora235/undotbs01.dbf tag=TAG20180816T232159 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35 channel ORA_DISK_1: starting datafile copy input datafile file number=00004 name=/u01/app/oradata/ora235/users01.dbf output file name=/u01/app/oradata/ora235/users01.dbf tag=TAG20180816T232159 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 16-AUG-18 sql statement: alter system archive log current contents of Memory Script: { backup as copy reuse archivelog like? "/data/arch/1_7_984178575.dbf" auxiliary format "/data/arch/1_7_984178575.dbf"? ; catalog clone archivelog? "/data/arch/1_7_984178575.dbf"; switch clone datafile all; } executing Memory Script Starting backup at 16-AUG-18 using channel ORA_DISK_1 channel ORA_DISK_1: starting archived log copy input archived log thread=1 sequence=7 RECID=45 STAMP=984353140 output file name=/data/arch/1_7_984178575.dbf RECID=0 STAMP=0 channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01 Finished backup at 16-AUG-18 cataloged archived log archived log file name=/data/arch/1_7_984178575.dbf RECID=45 STAMP=984353142 datafile 1 switched to datafile copy input datafile copy RECID=6 STAMP=984353142 file name=/u01/app/oradata/ora235/system01.dbf datafile 2 switched to datafile copy input datafile copy RECID=7 STAMP=984353142 file name=/u01/app/oradata/ora235/sysaux01.dbf datafile 3 switched to datafile copy input datafile copy RECID=8 STAMP=984353142 file name=/u01/app/oradata/ora235/undotbs01.dbf datafile 4 switched to datafile copy input datafile copy RECID=9 STAMP=984353142 file name=/u01/app/oradata/ora235/users01.dbf datafile 5 switched to datafile copy input datafile copy RECID=10 STAMP=984353142 file name=/data/oracle/data/lottu01.dbf contents of Memory Script: { set until scn? 1190640; recover clone database delete archivelog ; } executing Memory Script executing command: SET until clause Starting recover at 16-AUG-18 using channel ORA_AUX_DISK_1 starting media recovery archived log for thread 1 with sequence 7 is already on disk as file /data/arch/1_7_984178575.dbf archived log file name=/data/arch/1_7_984178575.dbf thread=1 sequence=7 media recovery complete, elapsed time: 00:00:00 Finished recover at 16-AUG-18 Oracle instance started Total System Global Area? ? 780824576 bytes Fixed Size? ? ? ? ? ? ? ? ? ? 2257312 bytes Variable Size? ? ? ? ? ? ? ? 511708768 bytes Database Buffers? ? ? ? ? ? 264241152 bytes Redo Buffers? ? ? ? ? ? ? ? ? 2617344 bytes contents of Memory Script: { sql clone "alter system set? db_name = ''ORA235'' comment= ''Reset to original value by RMAN'' scope=spfile"; sql clone "alter system reset? db_unique_name scope=spfile"; shutdown clone immediate; startup clone nomount; } executing Memory Script sql statement: alter system set? db_name =? ''ORA235'' comment= ''Reset to original value by RMAN'' scope=spfile sql statement: alter system reset? db_unique_name scope=spfile Oracle instance shut down connected to auxiliary database (not started) Oracle instance started Total System Global Area? ? 780824576 bytes Fixed Size? ? ? ? ? ? ? ? ? ? 2257312 bytes Variable Size? ? ? ? ? ? ? ? 511708768 bytes Database Buffers? ? ? ? ? ? 264241152 bytes Redo Buffers? ? ? ? ? ? ? ? ? 2617344 bytes sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ORA235" RESETLOGS ARCHIVELOG MAXLOGFILES? ? 16 MAXLOGMEMBERS? ? ? 3 MAXDATAFILES? ? ? 100 MAXINSTANCES? ? 8 MAXLOGHISTORY? ? ? 292 LOGFILE GROUP? 1 ( '/u01/app/oradata/ora235/redo01.log' ) SIZE 50 M? REUSE, GROUP? 2 ( '/u01/app/oradata/ora235/redo02.log' ) SIZE 50 M? REUSE, GROUP? 3 ( '/u01/app/oradata/ora235/redo03.log' ) SIZE 50 M? REUSE DATAFILE '/u01/app/oradata/ora235/system01.dbf' CHARACTER SET UTF8 contents of Memory Script: { set newname for tempfile? 1 to "/u01/app/oradata/ora235/temp01.dbf"; switch clone tempfile all; catalog clone datafilecopy? "/u01/app/oradata/ora235/sysaux01.dbf", "/u01/app/oradata/ora235/undotbs01.dbf", "/u01/app/oradata/ora235/users01.dbf", "/data/oracle/data/lottu01.dbf"; switch clone datafile all; } executing Memory Script executing command: SET NEWNAME renamed tempfile 1 to /u01/app/oradata/ora235/temp01.dbf in control file cataloged datafile copy datafile copy file name=/u01/app/oradata/ora235/sysaux01.dbf RECID=1 STAMP=984353151 cataloged datafile copy datafile copy file name=/u01/app/oradata/ora235/undotbs01.dbf RECID=2 STAMP=984353151 cataloged datafile copy datafile copy file name=/u01/app/oradata/ora235/users01.dbf RECID=3 STAMP=984353151 cataloged datafile copy datafile copy file name=/data/oracle/data/lottu01.dbf RECID=4 STAMP=984353151 datafile 2 switched to datafile copy input datafile copy RECID=1 STAMP=984353151 file name=/u01/app/oradata/ora235/sysaux01.dbf datafile 3 switched to datafile copy input datafile copy RECID=2 STAMP=984353151 file name=/u01/app/oradata/ora235/undotbs01.dbf datafile 4 switched to datafile copy input datafile copy RECID=3 STAMP=984353151 file name=/u01/app/oradata/ora235/users01.dbf datafile 5 switched to datafile copy input datafile copy RECID=4 STAMP=984353151 file name=/data/oracle/data/lottu01.dbf Reenabling controlfile options for auxiliary database Executing: alter database force logging contents of Memory Script: { Alter clone database open resetlogs; } executing Memory Script database opened Executing: alter database flashback on Finished Duplicate Db at 16-AUG-18