使用RMAN DUPLICATE复制数据库(Oracle 11g)到本机

--使用RMAN DUPLICATE复制数据库(Oracle 11g)到本机


--11g新功能：对正在运行的数据库进行克隆，不需要备份。


--步骤
1.创建辅助实例的Oracle口令文件。
2.建立到辅助实例的Oracle Net连接(监听文件增加库的静态注册，本地命名增加本地远程库的名字）。
3.创建辅助实例的初始化参数文件，并创建相关目录。
4.在NOMOUNT模式下启动辅助实例。
5.装载或打开目标数据库。
6.确保归档重做日志文件可用。
7.根据需要分配辅助通道。
8.执行DUPLICATE命令。




--实验

--将本机wyzc11g克隆到本机d11g

--修改网络配置文件--由于本机11g是安装在grid下的，所以监听文件在grid目录下，tns文件在db目录下。[oracle@luo ~]$ vi /u01/grid/11g/network/admin/listener.ora --添加以下段落(SID_DESC=(GLOBAL_DBNAME=d11g)(SID_NAME=d11g)(ORACLE_HOME=/u01/oracle/11g))[oracle@luo ~]$ lsnrctl stopLSNRCTL for Linux: Version 11.2.0.3.0 - Production on 11-JAN-2017 16:10:35Copyright (c) 1991, 2011, Oracle.  All rights reserved.Connecting to (ADDRESS=(PROTOCOL=TCP)(HOST=luo)(PORT=1523))TNS-12541: TNS:no listener TNS-12560: TNS:protocol adapter error  TNS-00511: No listener   Linux Error: 111: Connection refusedConnecting to (ADDRESS=(PROTOCOL=IPC)(KEY=PNPKEY))TNS-12541: TNS:no listener TNS-12560: TNS:protocol adapter error  TNS-00511: No listener   Linux Error: 111: Connection refused[oracle@luo ~]$ lsnrctl startLSNRCTL for Linux: Version 11.2.0.3.0 - Production on 11-JAN-2017 16:10:39Copyright (c) 1991, 2011, Oracle.  All rights reserved.Starting /u01/grid/11g/bin/tnslsnr: please wait...TNSLSNR for Linux: Version 11.2.0.3.0 - ProductionSystem parameter file is /u01/grid/11g/network/admin/listener.oraLog messages written to /u01/oracle/diag/tnslsnr/luo/listener/alert/log.xmlListening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=luo)(PORT=1523)))Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))Connecting to (ADDRESS=(PROTOCOL=TCP)(HOST=luo)(PORT=1523))STATUS of the LISTENER------------------------Alias                     LISTENERVersion                   TNSLSNR for Linux: Version 11.2.0.3.0 - ProductionStart Date                11-JAN-2017 16:10:41Uptime                    0 days 0 hr. 0 min. 1 secTrace Level               offSecurity                  ON: Local OS AuthenticationSNMP                      OFFListener Parameter File   /u01/grid/11g/network/admin/listener.oraListener Log File         /u01/oracle/diag/tnslsnr/luo/listener/alert/log.xmlListening Endpoints Summary...  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=luo)(PORT=1523)))  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))Services Summary...Service "d11g" has 1 instance(s).  Instance "d11g", status UNKNOWN, has 1 handler(s) for this service...Service "wyzc11g" has 1 instance(s).  Instance "wyzc11g", status UNKNOWN, has 1 handler(s) for this service...The command completed successfully[oracle@luo ~]$ db11g [oracle@luo ~]$ vi /u01/oracle/11g/network/admin/tnsnames.ora   --添加以下段落D11G=         (DESCRIPTION=                (ADDRESS_LIST=                        (ADDRESS=(PROTOCOL=tcp)(HOST=luo)(PORT=1523))                )                (CONNECT_DATA=                        (SERVICE_NAME=d11g)                        (SERVER=DEDICATED)                )        )[oracle@luo ~]$ tnsping wyzc11gTNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 11-JAN-2017 16:12:07Copyright (c) 1997, 2011, Oracle.  All rights reserved.Used parameter files:/u01/oracle/11g/network/admin/sqlnet.oraUsed TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(HOST=luo)(PORT=1523))) (CONNECT_DATA= (SERVICE_NAME=wyzc11g) (SERVER=DEDICATED)))OK (50 msec)[oracle@luo ~]$ tnsping d11gTNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 11-JAN-2017 16:12:12Copyright (c) 1997, 2011, Oracle.  All rights reserved.Used parameter files:/u01/oracle/11g/network/admin/sqlnet.oraUsed TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(HOST=luo)(PORT=1523))) (CONNECT_DATA= (SERVICE_NAME=d11g) (SERVER=DEDICATED)))OK (10 msec)--修改参数文件[oracle@luo ~]$ cd /u01/oracle/11g/dbs/[oracle@luo dbs]$ orapwd file=orapwd11g password=oracle[oracle@luo dbs]$ strings spfilewyzc11g.ora > initd11g.ora[oracle@luo dbs]$ vi initd11g.ora [oracle@luo dbs]$ grep d11g initd11g.ora d11g.__db_cache_size=444596224d11g.__java_pool_size=4194304d11g.__large_pool_size=4194304d11g.__oracle_base='/u01/oracle'#ORACLE_BASE set from environmentd11g.__pga_aggregate_target=180355072d11g.__sga_target=897581056d11g.__shared_io_pool_size=0d11g.__shared_pool_size=427819008d11g.__streams_pool_size=4194304*.audit_file_dest='/u01/oracle/admin/d11g/adump'*.control_files='/u01/oracle/oradata/d11g/control01.ctl','/u01/oracle/fast_recovery_area/d11g/control02.ctl'*.db_name='d11g'*.dispatchers='(PROTOCOL=TCP) (SERVICE=d11gXDB)'[oracle@luo dbs]$ vi initd11g.ora [oracle@luo dbs]$ mkdir /u01/oracle/admin/d11g/adump -p[oracle@luo dbs]$ mkdir /u01/oracle/oradata/d11g [oracle@luo dbs]$ mkdir /u01/oracle/fast_recovery_area/d11g[oracle@luo dbs]$ [oracle@luo dbs]$ export ORACLE_SID=d11g[oracle@luo dbs]$ sqlplus  / as sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 11 16:16:35 2017Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to an idle instance.SQL> startup nomountORACLE instance started.Total System Global Area  893562880 bytesFixed Size    2233520 bytesVariable Size  440404816 bytesDatabase Buffers  444596224 bytesRedo Buffers    6328320 bytesSQL> create spfile from pfile;File created.SQL> startup force nomountORACLE instance started.Total System Global Area  893562880 bytesFixed Size    2233520 bytesVariable Size  440404816 bytesDatabase Buffers  444596224 bytesRedo Buffers    6328320 bytesSQL> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, Oracle Label Security, OLAP, Data Mining,Oracle Database Vault and Real Application Testing options--查找数据文件路径[oracle@luo ~]$ db11g [oracle@luo ~]$ rlwrap sqlplus / as sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 11 16:18:15 2017Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to an idle instance.SQL> startupORACLE instance started.Total System Global Area  893562880 bytesFixed Size    2233520 bytesVariable Size  440404816 bytesDatabase Buffers  444596224 bytesRedo Buffers    6328320 bytesDatabase mounted.Database opened.SQL> select name from v$datafile;NAME--------------------------------------------------------------------------------/u01/oracle/oradata/wyzc11g/system01.dbf/u01/oracle/oradata/wyzc11g/sysaux01.dbf/u01/oracle/oradata/wyzc11g/undotbs01.dbf/u01/oracle/oradata/wyzc11g/users01.dbf/u01/oracle/oradata/wyzc11g/example01.dbf--克隆，新加参数from active database.RMAN> duplicate target database to "D11G" nofilenamecheck from active database 2> db_file_name_convert ('/u01/oracle/oradata/wyzc11g/','/u01/oracle/oradata/d11g/')3> logfile '/u01/oracle/oradata/d11g/redo01.log' size 10m,'/u01/oracle/oradata/d11g/redo02.log' size 10m;Starting Duplicate Db at 11-JAN-17using channel ORA_AUX_DISK_1contents of Memory Script:{   sql clone "alter system set  db_name =  ''WYZC11G'' comment= ''Modified by RMAN duplicate'' scope=spfile";   sql clone "alter system set  db_unique_name =  ''D11G'' comment= ''Modified by RMAN duplicate'' scope=spfile";   shutdown clone immediate;   startup clone force nomount   backup as copy current controlfile auxiliary format  '/u01/oracle/oradata/d11g/control01.ctl';   restore clone controlfile to  '/u01/oracle/fast_recovery_area/d11g/control02.ctl' from  '/u01/oracle/oradata/d11g/control01.ctl';   alter clone database mount;}executing Memory Scriptsql statement: alter system set  db_name =  ''WYZC11G'' comment= ''Modified by RMAN duplicate'' scope=spfilesql statement: alter system set  db_unique_name =  ''D11G'' comment= ''Modified by RMAN duplicate'' scope=spfileOracle instance shut downOracle instance startedTotal System Global Area     893562880 bytesFixed Size                     2233520 bytesVariable Size                440404816 bytesDatabase Buffers             444596224 bytesRedo Buffers                   6328320 bytesStarting backup at 11-JAN-17allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=39 device type=DISKchannel ORA_DISK_1: starting datafile copycopying current control fileoutput file name=/u01/oracle/11g/dbs/snapcf_wyzc11g.f tag=TAG20170111T162852 RECID=3 STAMP=933006532channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01Finished backup at 11-JAN-17Starting restore at 11-JAN-17allocated channel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: SID=18 device type=DISKchannel ORA_AUX_DISK_1: copied control file copyFinished restore at 11-JAN-17database mountedcontents of Memory Script:{   set newname for datafile  1 to  "/u01/oracle/oradata/d11g/system01.dbf";   set newname for datafile  2 to  "/u01/oracle/oradata/d11g/sysaux01.dbf";   set newname for datafile  3 to  "/u01/oracle/oradata/d11g/undotbs01.dbf";   set newname for datafile  4 to  "/u01/oracle/oradata/d11g/users01.dbf";   set newname for datafile  5 to  "/u01/oracle/oradata/d11g/example01.dbf";   backup as copy reuse   datafile  1 auxiliary format  "/u01/oracle/oradata/d11g/system01.dbf"   datafile  2 auxiliary format  "/u01/oracle/oradata/d11g/sysaux01.dbf"   datafile  3 auxiliary format  "/u01/oracle/oradata/d11g/undotbs01.dbf"   datafile  4 auxiliary format  "/u01/oracle/oradata/d11g/users01.dbf"   datafile  5 auxiliary format  "/u01/oracle/oradata/d11g/example01.dbf"   ;   sql 'alter system archive log current';}executing Memory Scriptexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEStarting backup at 11-JAN-17using channel ORA_DISK_1channel ORA_DISK_1: starting datafile copyinput datafile file number=00001 name=/u01/oracle/oradata/wyzc11g/system01.dbfoutput file name=/u01/oracle/oradata/d11g/system01.dbf tag=TAG20170111T162859channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25channel ORA_DISK_1: starting datafile copyinput datafile file number=00002 name=/u01/oracle/oradata/wyzc11g/sysaux01.dbfoutput file name=/u01/oracle/oradata/d11g/sysaux01.dbf tag=TAG20170111T162859channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:16channel ORA_DISK_1: starting datafile copyinput datafile file number=00005 name=/u01/oracle/oradata/wyzc11g/example01.dbfoutput file name=/u01/oracle/oradata/d11g/example01.dbf tag=TAG20170111T162859channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15channel ORA_DISK_1: starting datafile copyinput datafile file number=00003 name=/u01/oracle/oradata/wyzc11g/undotbs01.dbfoutput file name=/u01/oracle/oradata/d11g/undotbs01.dbf tag=TAG20170111T162859channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07channel ORA_DISK_1: starting datafile copyinput datafile file number=00004 name=/u01/oracle/oradata/wyzc11g/users01.dbfoutput file name=/u01/oracle/oradata/d11g/users01.dbf tag=TAG20170111T162859channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03Finished backup at 11-JAN-17sql statement: alter system archive log currentcontents of Memory Script:{   backup as copy reuse   archivelog like  "/u01/oracle/fast_recovery_area/WYZC11G/archivelog/2017_01_11/o1_mf_1_95_d7cv8fz0_.arc" auxiliary format  "/u01/oracle/fast_recovery_area/D11G/archivelog/2017_01_11/o1_mf_1_95_%u_.arc"   ;   catalog clone recovery area;   switch clone datafile all;}executing Memory ScriptStarting backup at 11-JAN-17using channel ORA_DISK_1channel ORA_DISK_1: starting archived log copyinput archived log thread=1 sequence=95 RECID=8 STAMP=933006606output file name=/u01/oracle/fast_recovery_area/D11G/archivelog/2017_01_11/o1_mf_1_95_0erpp38e_.arc RECID=0 STAMP=0channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01Finished backup at 11-JAN-17searching for all files in the recovery areaList of Files Unknown to the Database=====================================File Name: /u01/oracle/fast_recovery_area/D11G/archivelog/2017_01_11/o1_mf_1_95_0erpp38e_.arccataloging files...cataloging doneList of Cataloged Files=======================File Name: /u01/oracle/fast_recovery_area/D11G/archivelog/2017_01_11/o1_mf_1_95_0erpp38e_.arcdatafile 1 switched to datafile copyinput datafile copy RECID=3 STAMP=933006607 file name=/u01/oracle/oradata/d11g/system01.dbfdatafile 2 switched to datafile copyinput datafile copy RECID=4 STAMP=933006607 file name=/u01/oracle/oradata/d11g/sysaux01.dbfdatafile 3 switched to datafile copyinput datafile copy RECID=5 STAMP=933006607 file name=/u01/oracle/oradata/d11g/undotbs01.dbfdatafile 4 switched to datafile copyinput datafile copy RECID=6 STAMP=933006607 file name=/u01/oracle/oradata/d11g/users01.dbfdatafile 5 switched to datafile copyinput datafile copy RECID=7 STAMP=933006607 file name=/u01/oracle/oradata/d11g/example01.dbfcontents of Memory Script:{   set until scn  2593006;   recover   clone database    delete archivelog   ;}executing Memory Scriptexecuting command: SET until clauseStarting recover at 11-JAN-17using channel ORA_AUX_DISK_1starting media recoveryarchived log for thread 1 with sequence 95 is already on disk as file /u01/oracle/fast_recovery_area/D11G/archivelog/2017_01_11/o1_mf_1_95_0erpp38e_.arcarchived log file name=/u01/oracle/fast_recovery_area/D11G/archivelog/2017_01_11/o1_mf_1_95_0erpp38e_.arc thread=1 sequence=95media recovery complete, elapsed time: 00:00:00Finished recover at 11-JAN-17Oracle instance startedTotal System Global Area     893562880 bytesFixed Size                     2233520 bytesVariable Size                440404816 bytesDatabase Buffers             444596224 bytesRedo Buffers                   6328320 bytescontents of Memory Script:{   sql clone "alter system set  db_name =  ''D11G'' 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 Scriptsql statement: alter system set  db_name =  ''D11G'' comment= ''Reset to original value by RMAN'' scope=spfilesql statement: alter system reset  db_unique_name scope=spfileOracle instance shut downconnected to auxiliary database (not started)Oracle instance startedTotal System Global Area     893562880 bytesFixed Size                     2233520 bytesVariable Size                440404816 bytesDatabase Buffers             444596224 bytesRedo Buffers                   6328320 bytessql statement: CREATE CONTROLFILE REUSE SET DATABASE "D11G" RESETLOGS ARCHIVELOG   MAXLOGFILES     16  MAXLOGMEMBERS      3  MAXDATAFILES      100  MAXINSTANCES     8  MAXLOGHISTORY      292 LOGFILE  GROUP   1 '/u01/oracle/oradata/d11g/redo01.log' SIZE 10 M ,  GROUP   2 '/u01/oracle/oradata/d11g/redo02.log' SIZE 10 M  DATAFILE  '/u01/oracle/oradata/d11g/system01.dbf' CHARACTER SET AL32UTF8contents of Memory Script:{   set newname for tempfile  1 to  "/u01/oracle/oradata/d11g/temp01.dbf";   switch clone tempfile all;   catalog clone datafilecopy  "/u01/oracle/oradata/d11g/sysaux01.dbf",  "/u01/oracle/oradata/d11g/undotbs01.dbf",  "/u01/oracle/oradata/d11g/users01.dbf",  "/u01/oracle/oradata/d11g/example01.dbf";   switch clone datafile all;}executing Memory Scriptexecuting command: SET NEWNAMErenamed tempfile 1 to /u01/oracle/oradata/d11g/temp01.dbf in control filecataloged datafile copydatafile copy file name=/u01/oracle/oradata/d11g/sysaux01.dbf RECID=1 STAMP=933006617cataloged datafile copydatafile copy file name=/u01/oracle/oradata/d11g/undotbs01.dbf RECID=2 STAMP=933006617cataloged datafile copydatafile copy file name=/u01/oracle/oradata/d11g/users01.dbf RECID=3 STAMP=933006617cataloged datafile copydatafile copy file name=/u01/oracle/oradata/d11g/example01.dbf RECID=4 STAMP=933006617datafile 2 switched to datafile copyinput datafile copy RECID=1 STAMP=933006617 file name=/u01/oracle/oradata/d11g/sysaux01.dbfdatafile 3 switched to datafile copyinput datafile copy RECID=2 STAMP=933006617 file name=/u01/oracle/oradata/d11g/undotbs01.dbfdatafile 4 switched to datafile copyinput datafile copy RECID=3 STAMP=933006617 file name=/u01/oracle/oradata/d11g/users01.dbfdatafile 5 switched to datafile copyinput datafile copy RECID=4 STAMP=933006617 file name=/u01/oracle/oradata/d11g/example01.dbfcontents of Memory Script:{   Alter clone database open resetlogs;}executing Memory Scriptdatabase openedFinished Duplicate Db at 11-JAN-17--克隆完成。[oracle@luo ~]$ export ORACLE_SID=d11g[oracle@luo ~]$ rlwrap sqlplus / as sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 11 16:32:22 2017Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, Oracle Label Security, OLAP, Data Mining,Oracle Database Vault and Real Application Testing optionsSQL> select open_mode from v$database;OPEN_MODE--------------------READ WRITESQL> select name from v$datafile;NAME--------------------------------------------------------------------------------/u01/oracle/oradata/d11g/system01.dbf/u01/oracle/oradata/d11g/sysaux01.dbf/u01/oracle/oradata/d11g/undotbs01.dbf/u01/oracle/oradata/d11g/users01.dbf/u01/oracle/oradata/d11g/example01.dbfSQL> select member from v$logfile;MEMBER--------------------------------------------------------------------------------/u01/oracle/oradata/d11g/redo02.log/u01/oracle/oradata/d11g/redo01.log