Duplicate standby database from active database-白红宇

Duplicate standby database from active database
阅读量：6820 次
发布时间：2019-06-26
本文共 16503 字，大约阅读时间需要 55 分钟。
11g Release1 中引入了新的RMAN duplicate命令，即duplicate from active database命令。利用该命令可以更加便捷地创建Data Guard环境，你甚至不需要将Primary Database shutdown(整个过程中主库都可以处于打开状态下)，也不需要在配置前做一些额外的备份操作，仅需要配置起auxiliary辅助实例，同时创建密码文件，并在监听(LISTENER)中加入静态注册信息后就可以开始工作了！以下示例中我们会在Primary Database所在的同一台服务器上部署一套物理备库(Physical Standby)，实际上这样比在不同的2台服务器上配置Data Guard要复杂些，原因是那样的话我们无需做File Name的转换，具体的环境：
Database Role	DB_UNIQUE_NAME
Primary Database	PROD
Standby Database	SBDB
/* 需要注意的是DG环境中force logging,不要用了新特性就将这个基本的要求忘记了 ！ */SQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit ProductionPL/SQL Release 11.2.0.2.0 - ProductionCORE	11.2.0.2.0	ProductionTNS for Linux: Version 11.2.0.2.0 - ProductionNLSRTL Version 11.2.0.2.0 - Production/* 显然PROD主库应当处于归档模式并且是FORCE LOGGING状态下 */SQL> alter database force logging;Database altered.同时在Primary Database上创建必要的standby logfile:SQL> alter database add standby logfile group 7 size 50M;Database altered.SQL> alter database add standby logfile group 8 size 50M;Database altered. ............../* standby实例只需要db_name参数就可以启动到nomount模式了，   并为standby数据库创建密码文件 */[maclean@rh6 ~]$ echo "db_name=PROD" >$ORACLE_HOME/dbs/initSBDB.ora[maclean@rh6 ~]$ orapwd file=$ORACLE_HOME/dbs/orapwSBDB password=XXXX entries=10[maclean@rh6 ~]$ cd $ORACLE_HOME/network/admin我们需要保证PROD和SBDB实例在监听器Listener中被静态注册，同时也包括DGMGRL需要用到的*_DGMGRL服务名[maclean@rh6 admin]$ cat listener.ora# listener23920.ora Network Configuration File: /tmp/listener23920.ora# Generated by Oracle configuration tools.DGLSN =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = rh6.oracle.com)(PORT = 1588))  )SID_LIST_DGLSN =  (SID_LIST =    (SID_DESC =      (GLOBAL_DBNAME = PROD)      (ORACLE_HOME = /s01/product/11.2.0/dbhome_2)      (SID_NAME = PROD)    )    (SID_DESC =      (GLOBAL_DBNAME = SBDB)      (ORACLE_HOME = /s01/product/11.2.0/dbhome_2)      (SID_NAME = SBDB)    )    (SID_DESC =      (GLOBAL_DBNAME = PROD_DGMGRL)      (ORACLE_HOME = /s01/product/11.2.0/dbhome_2)      (SID_NAME = PROD)    )    (SID_DESC =      (GLOBAL_DBNAME = SBDB_DGMGRL)      (ORACLE_HOME = /s01/product/11.2.0/dbhome_2)      (SID_NAME = SBDB)    )  )/* 并在tnsnames.ora文件中加入必要的服务别名 */[maclean@rh6 admin]$ cat tnsnames.ora# tnsnames.ora Network Configuration File: /s01/product/11.2.0/dbhome_2/network/admin/tnsnames.ora# Generated by Oracle configuration tools.PROD =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = rh6.oracle.com)(PORT = 1588))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = PROD)    )  )SBDB =  (DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)(HOST = rh6.oracle.com)(PORT = 1588))    )    (CONNECT_DATA =      (SERVICE_NAME = SBDB)    )  )DGLSN =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = rh6.oracle.com)(PORT = 1588))  )接着我们来启动辅助实例[maclean@rh6 ~]$ export ORACLE_SID=SBDB[maclean@rh6 ~]$ sqlplus  / as sysdbaSQL> startup  nomount;ORACLE instance started./* 使用远程登录测试是否可以以SYSDBA登录SBDB实例 */[maclean@rh6 ~]$ sqlplus  sys/XXXXXX@SBDB as sysdba下面我们需要准备一份duplicate standby database的脚本，因为我们是在同一台主机上配置Data Guard所以这里要用到db_file_name_convert和log_file_name_convert，如果你的环境中不需要这么做那么你可以指定NOFILENAMECHECK；以下为示例的脚本，可以看到它并不复杂只是指定了必要的初始化参数，十分易于记忆。[maclean@rh6 ~]$ cat duplicate_act_standby.rcv duplicate target databasefor standbyfrom active databaseDORECOVERspfileset db_unique_name='SBDB'set log_archive_dest_1='location=/standby/arch02'set log_archive_dest_2='service=PROD async lgwr register valid_for=(online_logfile,primary_role) db_unique_name=PROD'set standby_file_management='AUTO'set fal_server='PROD'set fal_client='SBDB'set control_files='/standby/oradata/SBDB/controlfile/control1.ctl','/standby/oradata/SBDB/controlfile/control2.ctl'set db_file_name_convert='PROD','SBDB'set log_file_name_convert='PROD','SBDB'set memory_target='0'set sga_target='400M';具体执行以上脚本，我们需要同时登录target database PROD和auxiliary instance辅助实例SBDB:[maclean@rh6 ~]$ echo $ORACLE_SIDPROD[maclean@rh6 ~]$ rman target / auxiliary sys/oracle@SBDB cmdfile=duplicate_act_standby.rcv log=das.logconnected to target database: PROD (DBID=158660885)connected to auxiliary database: PROD (not mounted)RMAN> duplicate target database2> for standby3> from active database4> DORECOVER5> spfile6> set db_unique_name='SBDB'7> set log_archive_dest_1='location=/standby/arch02'8> set log_archive_dest_2='service=PROD async lgwr register valid_for=(online_logfile,primary_role) db_unique_name=PROD'9> set standby_file_management='AUTO'10> set fal_server='PROD'11> set fal_client='SBDB'12> set control_files='/standby/oradata/SBDB/controlfile/control1.ctl','/standby/oradata/SBDB/controlfile/control2.ctl'13> set db_file_name_convert='PROD','SBDB'14> set log_file_name_convert='PROD','SBDB'15> set memory_target='0'16> set sga_target='400M';17>Starting Duplicate Db at 26-MAR-11using target database control file instead of recovery catalogallocated channel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: SID=96 device type=DISKcontents of Memory Script:{   backup as copy reuse   targetfile  '/s01/product/11.2.0/dbhome_2/dbs/orapwPROD' auxiliary format '/s01/product/11.2.0/dbhome_2/dbs/orapwSBDB'   targetfile '/s01/product/11.2.0/dbhome_2/dbs/spfilePROD.ora' auxiliary format '/s01/product/11.2.0/dbhome_2/dbs/spfileSBDB.ora'   ;   sql clone "alter system set spfile= ''/s01/product/11.2.0/dbhome_2/dbs/spfileSBDB.ora''";}executing Memory ScriptStarting backup at 26-MAR-11allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=17 device type=DISKFinished backup at 26-MAR-11sql statement: alter system set spfile= ''/s01/product/11.2.0/dbhome_2/dbs/spfileSBDB.ora''contents of Memory Script:{   sql clone "alter system set  db_unique_name = ''SBDB'' comment= '''' scope=spfile";   sql clone "alter system set  log_archive_dest_1 = ''location=/standby/arch02'' comment= '''' scope=spfile";   sql clone "alter system set  log_archive_dest_2 = ''service=PROD async lgwr register valid_for=(online_logfile,primary_role) db_unique_name=PROD'' comment= '''' scope=spfile";   sql clone "alter system set  standby_file_management = ''AUTO'' comment= '''' scope=spfile";   sql clone "alter system set  fal_server = ''PROD'' comment= '''' scope=spfile";   sql clone "alter system set  fal_client = ''SBDB'' comment= '''' scope=spfile";   sql clone "alter system set  control_files = ''/standby/oradata/SBDB/controlfile/control1.ctl'', ''/standby/oradata/SBDB/controlfile/control2.ctl'' comment= '''' scope=spfile";   sql clone "alter system set  db_file_name_convert = ''PROD'', ''SBDB'' comment= '''' scope=spfile";   sql clone "alter system set  log_file_name_convert = ''PROD'', ''SBDB'' comment= '''' scope=spfile";   sql clone "alter system set  memory_target = 0 comment= '''' scope=spfile";   sql clone "alter system set  sga_target = 400M comment= '''' scope=spfile";   shutdown clone immediate;   startup clone nomount;}executing Memory Scriptsql statement: alter system set  db_unique_name =  ''SBDB'' comment= '''' scope=spfilesql statement: alter system set  log_archive_dest_1 =  ''location=/standby/arch02'' comment= '''' scope=spfilesql statement: alter system set  log_archive_dest_2 =  ''service=PROD async lgwr register valid_for=(online_logfile,primary_role) db_unique_name=PROD'' comment= '''' scope=spfilesql statement: alter system set  standby_file_management =  ''AUTO'' comment= '''' scope=spfilesql statement: alter system set  fal_server =  ''PROD'' comment= '''' scope=spfilesql statement: alter system set  fal_client =  ''SBDB'' comment= '''' scope=spfilesql statement: alter system set  control_files =  ''/standby/oradata/SBDB/controlfile/control1.ctl'',''/standby/oradata/SBDB/controlfile/control2.ctl''comment= '''' scope=spfilesql statement: alter system set  db_file_name_convert =  ''PROD'', ''SBDB'' comment= '''' scope=spfilesql statement: alter system set  log_file_name_convert =  ''PROD'', ''SBDB'' comment= '''' scope=spfilesql statement: alter system set  memory_target =  0 comment= '''' scope=spfilesql statement: alter system set  sga_target =  400M comment= '''' scope=spfileOracle instance shut downconnected to auxiliary database (not started)Oracle instance startedTotal System Global Area     417546240 bytesFixed Size                     2227072 bytesVariable Size                134218880 bytesDatabase Buffers             272629760 bytesRedo Buffers                   8470528 bytescontents of Memory Script:{   backup as copy current controlfile for standby auxiliary format  '/standby/oradata/SBDB/controlfile/control1.ctl';   restore clone controlfile to  '/standby/oradata/SBDB/controlfile/control2.ctl' from '/standby/oradata/SBDB/controlfile/control1.ctl';}executing Memory ScriptStarting backup at 26-MAR-11using channel ORA_DISK_1channel ORA_DISK_1: starting datafile copycopying standby control fileoutput file name=/s01/product/11.2.0/dbhome_2/dbs/snapcf_PROD.f tag=TAG20110326T195144 RECID=2 STAMP=746826704channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01Finished backup at 26-MAR-11Starting restore at 26-MAR-11allocated channel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: SID=134 device type=DISKchannel ORA_AUX_DISK_1: copied control file copyFinished restore at 26-MAR-11contents of Memory Script:{   sql clone 'alter database mount standby database';}executing Memory Scriptsql statement: alter database mount standby databasecontents of Memory Script:{   set newname for tempfile  1 to "/standby/oradata/SBDB/datafile/o1_mf_temp_6rvjsmr4_.tmp";   switch clone tempfile all;   set newname for datafile  1 to "/standby/oradata/SBDB/datafile/o1_mf_system_6rvjrtxh_.dbf";   set newname for datafile  2 to "/standby/oradata/SBDB/datafile/o1_mf_sysaux_6rvjs6vh_.dbf";   set newname for datafile  3 to "/standby/oradata/SBDB/datafile/o1_mf_undotbs1_6rvjsjjg_.dbf";   set newname for datafile  4 to "/standby/oradata/SBDB/datafile/o1_mf_users_6rvjsy5q_.dbf";   backup as copy reuse   datafile  1 auxiliary format "/standby/oradata/SBDB/datafile/o1_mf_system_6rvjrtxh_.dbf"   datafile 2 auxiliary format "/standby/oradata/SBDB/datafile/o1_mf_sysaux_6rvjs6vh_.dbf"   datafile 3 auxiliary format "/standby/oradata/SBDB/datafile/o1_mf_undotbs1_6rvjsjjg_.dbf"   datafile 4 auxiliary format "/standby/oradata/SBDB/datafile/o1_mf_users_6rvjsy5q_.dbf"   ;   sql 'alter system archive log current';}executing Memory Scriptexecuting command: SET NEWNAMErenamed tempfile 1 to /standby/oradata/SBDB/datafile/o1_mf_temp_6rvjsmr4_.tmp in control fileexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEStarting backup at 26-MAR-11using channel ORA_DISK_1channel ORA_DISK_1: starting datafile copyinput datafile file number=00001 name=/standby/oradata/PROD/datafile/o1_mf_system_6rvjrtxh_.dbfoutput file name=/standby/oradata/SBDB/datafile/o1_mf_system_6rvjrtxh_.dbf tag=TAG20110326T195152channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15channel ORA_DISK_1: starting datafile copyinput datafile file number=00002 name=/standby/oradata/PROD/datafile/o1_mf_sysaux_6rvjs6vh_.dbfoutput file name=/standby/oradata/SBDB/datafile/o1_mf_sysaux_6rvjs6vh_.dbf tag=TAG20110326T195152channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15channel ORA_DISK_1: starting datafile copyinput datafile file number=00003 name=/standby/oradata/PROD/datafile/o1_mf_undotbs1_6rvjsjjg_.dbfoutput file name=/standby/oradata/SBDB/datafile/o1_mf_undotbs1_6rvjsjjg_.dbf tag=TAG20110326T195152channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07channel ORA_DISK_1: starting datafile copyinput datafile file number=00004 name=/standby/oradata/PROD/datafile/o1_mf_users_6rvjsy5q_.dbfoutput file name=/standby/oradata/SBDB/datafile/o1_mf_users_6rvjsy5q_.dbf tag=TAG20110326T195152channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01Finished backup at 26-MAR-11sql statement: alter system archive log currentcontents of Memory Script:{   backup as copy reuse   archivelog like  "/standby/arch01/1_17_746822549.dbf" auxiliary format "/standby/arch02/1_17_746822549.dbf"   ;   catalog clone archivelog  "/standby/arch02/1_17_746822549.dbf";   switch clone datafile all;}executing Memory ScriptStarting backup at 26-MAR-11using channel ORA_DISK_1channel ORA_DISK_1: starting archived log copyinput archived log thread=1 sequence=17 RECID=3 STAMP=746826751output file name=/standby/arch02/1_17_746822549.dbf RECID=0 STAMP=0channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01Finished backup at 26-MAR-11cataloged archived logarchived log file name=/standby/arch02/1_17_746822549.dbf RECID=1 STAMP=746826752datafile 1 switched to datafile copyinput datafile copy RECID=2 STAMP=746826752 file name=/standby/oradata/SBDB/datafile/o1_mf_system_6rvjrtxh_.dbfdatafile 2 switched to datafile copyinput datafile copy RECID=3 STAMP=746826752 file name=/standby/oradata/SBDB/datafile/o1_mf_sysaux_6rvjs6vh_.dbfdatafile 3 switched to datafile copyinput datafile copy RECID=4 STAMP=746826752 file name=/standby/oradata/SBDB/datafile/o1_mf_undotbs1_6rvjsjjg_.dbfdatafile 4 switched to datafile copyinput datafile copy RECID=5 STAMP=746826752 file name=/standby/oradata/SBDB/datafile/o1_mf_users_6rvjsy5q_.dbfcontents of Memory Script:{   set until scn  242517;   recover   standby   clone database    delete archivelog   ;}executing Memory Scriptexecuting command: SET until clauseStarting recover at 26-MAR-11using channel ORA_AUX_DISK_1starting media recoveryarchived log for thread 1 with sequence 17 is already on disk as file /standby/arch02/1_17_746822549.dbfarchived log file name=/standby/arch02/1_17_746822549.dbf thread=1 sequence=17media recovery complete, elapsed time: 00:00:00Finished recover at 26-MAR-11Finished Duplicate Db at 26-MAR-11Recovery Manager complete.[maclean@rh6 ~]$ export ORACLE_SID=PROD[maclean@rh6 ~]$ sqlplus  / as sysdba/* 在主库PROD上设置到物理备库SBDB的归档目的地 */SQL> alter system set log_archive_dest_2='service=sbdb lgwr async  valid_for=(online_logfiles,primary_role) db_unique_name=SBDB';System altered.以上完成了对物理备库Physical Standby的配置，紧接着我们来配合Data Broker:SQL> alter system set dg_broker_start=true;System altered.[maclean@rh6 ~]$ export ORACLE_SID=SBDB[maclean@rh6 ~]$ sqlplus  / as sysdbaSQL>  alter system set dg_broker_start=true;System altered.[maclean@rh6 ~]$ dgmgrl sys/oracle@PRODDGMGRL for Linux: Version 11.2.0.2.0 - 64bit ProductionCopyright (c) 2000, 2009, Oracle. All rights reserved.Welcome to DGMGRL, type "help" for information.Connected.DGMGRL>  create CONFIGURATION PROD as PRIMARY DATABASE IS PROD  CONNECT IDENTIFIER IS PROD;Configuration "prod" created with primary database "prod"DGMGRL> add database sbdb  AS CONNECT IDENTIFIER IS sbdb MAINTAINED AS PHYSICAL;Database "sbdb" addedDGMGRL> enable configuration;Enabled.DGMGRL> edit database prod set property LogXptMode='sync';Property "logxptmode" updatedDGMGRL> edit database sbdb set property LogXptMode='sync';Property "logxptmode" updated/* 修改当前DG的保护模式为最大可用模式MaxAvailability */DGMGRL> edit CONFIGURATION SET PROTECTION MODE  as MaxAvailability;Succeeded.DGMGRL> show configuration;Configuration - prod  Protection Mode: MaxAvailability  Databases:    prod - Primary database    sbdb - Physical standby database      Error: ORA-16766: Redo Apply is stoppedFast-Start Failover: DISABLEDConfiguration Status:ERROR/* 以上可以看到物理备库SBDB上的REDO APPLY被停止了，我们可以在DGMGRL中启动其REDO APPLY */DGMGRL> edit database sbdb set state='APPLY-ON';Succeeded.DGMGRL> show configuration;Configuration - prod  Protection Mode: MaxAvailability  Databases:    prod - Primary database    sbdb - Physical standby databaseFast-Start Failover: DISABLEDConfiguration Status:SUCCESS/* 以下使用DGMGRL来回切换主备库角色，十分方便 */DGMGRL> switchover to sbdb;Performing switchover NOW, please wait...New primary database "sbdb" is opening...Operation requires shutdown of instance "PROD" on database "prod"Shutting down instance "PROD"...ORACLE instance shut down.Operation requires startup of instance "PROD" on database "prod"Starting instance "PROD"...ORACLE instance started.Database mounted.Switchover succeeded, new primary is "sbdb"DGMGRL> switchover to prod;Performing switchover NOW, please wait...New primary database "prod" is opening...Operation requires shutdown of instance "SBDB" on database "sbdb"Shutting down instance "SBDB"...ORACLE instance shut down.Operation requires startup of instance "SBDB" on database "sbdb"Starting instance "SBDB"...ORACLE instance started.Database mounted.Switchover succeeded, new primary is "prod"
转载地址：http://omszl.baihongyu.com/
你可能感兴趣的文章