duplicate database for standby

[i=s] 本帖最后由 outdo 于 2013-3-20 11:38 编辑

duplicate database for standby 是快速创建从库的一种方式，好处是主库只需要重启一次来进行参数配置，下面是我简单整理的一个过程。

----1，创建密码文件，保持主从密码一致
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=outdo entries=10 force=y
--或者直接拷贝秘密文件
scp [url=mailto:root@10.12.135.99:/opt/oracle/product/11.2.0.2/dbs/orapwdevadol]root@10.12.135.99:/opt/oracle/product/11.2.0.2/dbs/orapwdevadol[/url] /opt/oracle/product/11.2.0.2/dbs/
----2，辅助实例采用pfile启动，如果是新库，直接创建参数文件进行启动
su - oracle
. .devadolprofile
sqlplus / as sysdba
create pfile='/home/oracle/init.ora' from spfile;
shutdown immediate;
startup nomount pfile='/home/oracle/init.ora';
exit
----3，配置网络，启动监听
--两边配置监听
vi $TNS_ADMIN/listener.ora
/*
SID_LIST_devadol3=(SID_LIST=(SID_DESC=(SID_NAME=devadol3)(ORACLE_HOME=/opt/oracle/product/11.2.0.2)))
devadol3=(DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1581))))
ADMIN_RESTRICTIONS_devadol3=ON
INBOUND_CONNECT_TIMEOUT_devadol3=120
SUBSCRIBE_FOR_NODE_DOWN_EVENT_devadol3=OFF
*/
lsnrctl status devadol3
--配置tnsname
vi tnsname.ora
/*
devadol3=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.12.135.103)(PORT=1581))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=devadol3)
))
sdevadol3=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.11.193.198)(PORT=1581))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=devadol3
)))
*/

tnsping devadol3
tnsping sdevadol3
----4，开始复制脚本
vi dupsdb.sh
/*
#!/bin/bash
#!/bin/sh
. .devadolprofile
rman target [url=mailto:sys/outdo@devadol3]sys/outdo@devadol3[/url] auxiliary [url=mailto:sys/outdo@sdevadol3]sys/outdo@sdevadol3[/url]  <run{
allocate channel p1 type disk;
allocate channel p2 type disk;
allocate channel p3 type disk;
allocate auxiliary channel s1 type disk;
allocate auxiliary channel s2 type disk;
allocate auxiliary channel s3 type disk;
DUPLICATE TARGET DATABASE
  FOR STANDBY
  FROM ACTIVE DATABASE
  DORECOVER
  SPFILE
   SET "db_unique_name"="sdevadol3"
    SET LOG_ARCHIVE_DEST_2="service=sdevadol3 LGWR ASYNC REGISTER valid_for=(all_logfiles,primary_role)"
  NOFILENAMECHECK;
release channel p1;
release channel p2;
release channel p3;
release channel s1;
release channel s2;
release channel s3;
}
exit;
EOF
*/
--授权执行
chmod u+x dupsdb.sh
nohup ./dupsdb.sh &
tail -f nohup.out

----5，创建standby logfile
select * from v$standby_log;
alter database recover managed standby database cancel;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 17 ('/data/oracle/oradata/devadol3/sredo17.rdo') SIZE 500M reuse;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 18 ('/data/oracle/oradata/devadol3/sredo18.rdo') SIZE 500M reuse;
--省略
ALTER DATABASE ADD STANDBY LOGFILE GROUP 33 ('/data/oracle/oradata/devadol3/sredo33.rdo') SIZE 500M reuse;
----6，启动恢复
select open_mode from v$database;
ALTER DATABASE OPEN;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
--主从验证
alter system switch logfile;
select *  from (select sequence# from v$log_history order by sequence# desc) where rownum <10;

----7，其他可用脚本
scp [url=mailto:root@10.12.135.101:/data/oracle/oradata/devadol3/archive/1_21869_771088367.dbf]root@10.12.135.101:/data/oracle/oradata/devadol3/archive/1_21869_771088367.dbf[/url] /data/oracle/oradata/devadol3/archive/
alter database register logfile '/data/oracle/oradata/devadol3/archive/1_21869_771088367.dbf';

----8，复复制整个过程

Recovery Manager: Release 11.2.0.2.0 - Production on Tue Mar 19 19:01:40 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ADONLINE (DBID=1427255695)
connected to auxiliary database: ADONLINE (not mounted)
using target database control file instead of recovery catalog
allocated channel: p1
channel p1: SID=21 device type=DISK

Starting Duplicate Db at 19-MAR-13
contents of Memory Script.:
{
   backup as copy reuse
   targetfile  '/opt/oracle/product/11.2.0.2/dbs/orapwdevadol3' auxiliary format
'/opt/oracle/product/11.2.0.2/dbs/orapwdevadol3'   targetfile
'/opt/oracle/product/11.2.0.2/dbs/spfiledevadol3.ora' auxiliary format
'/opt/oracle/product/11.2.0.2/dbs/spfiledevadol3.ora'   ;
   sql clone "alter system set spfile= ''/opt/oracle/product/11.2.0.2/dbs/spfiledevadol3.ora''";
}
executing Memory Script
Starting backup at 19-MAR-13
Finished backup at 19-MAR-13
sql statement: alter system set spfile= ''/opt/oracle/product/11.2.0.2/dbs/spfiledevadol3.ora''
contents of Memory Script.:
{
   sql clone "alter system set  db_unique_name =
''sdevadol3'' comment=
'''' scope=spfile";
   sql clone "alter system set  LOG_ARCHIVE_DEST_2 =
''service=sdevadol3 LGWR ASYNC REGISTER valid_for=(all_logfiles,primary_role)'' comment=
'''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script
sql statement: alter system set  db_unique_name =  ''sdevadol3'' comment= '''' scope=spfile
sql statement: alter system set  LOG_ARCHIVE_DEST_2 =  ''service=sdevadol3 LGWR ASYNC REGISTER valid_for=(all_logfiles,primary_role)'' comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area    8551575552 bytes
Fixed Size                     2243608 bytes
Variable Size               2046821352 bytes
Database Buffers            6442450944 bytes
Redo Buffers                  60059648 bytes
allocated channel: s1
channel s1: SID=5 device type=DISK
allocated channel: s2
channel s2: SID=761 device type=DISK
allocated channel: s3
channel s3: SID=1518 device type=DISK
contents of Memory Script.:
{
   backup as copy current controlfile for standby auxiliary format  '/data/oracle/oradata/devadol3/control01.ctl';
   restore clone controlfile to  '/data/oracle/oradata/devadol3/control02.ctl' from
'/data/oracle/oradata/devadol3/control01.ctl';
   restore clone controlfile to  '/data/oracle/oradata/devadol3/control03.ctl' from
'/data/oracle/oradata/devadol3/control01.ctl';
}
executing Memory Script
Starting backup at 19-MAR-13
channel p1: starting datafile copy
copying standby control file
output file name=/opt/oracle/product/11.2.0.2/dbs/snapcf_devadol3.f tag=TAG20130319T190250 RECID=3 STAMP=810500574
channel p1: datafile copy complete, elapsed time: 00:00:08
Finished backup at 19-MAR-13
Starting restore at 19-MAR-13
channel s2: skipped, AUTOBACKUP already found
channel s3: skipped, AUTOBACKUP already found
channel s1: copied control file copy
Finished restore at 19-MAR-13
Starting restore at 19-MAR-13
channel s2: skipped, AUTOBACKUP already found
channel s3: skipped, AUTOBACKUP already found
channel s1: copied control file copy
Finished restore at 19-MAR-13
contents of Memory Script.:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
RMAN-05538: WARNING: implicitly using DB_FILE_NAME_CONVERT
contents of Memory Script.:
{
   set newname for tempfile  1 to
"/data/oracle/oradata/devadol3/temp02.dbf";
   set newname for tempfile  2 to
"/data/oracle/oradata/devadol3/temp01.dbf";
   set newname for tempfile  3 to
"/data/oracle/oradata/devadol3/temp03.dbf";
   set newname for tempfile  4 to
"/data/oracle/oradata/devadol3/temp04.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to
"/data/oracle/oradata/devadol3/system01.dbf";
   set newname for datafile  2 to
"/data/oracle/oradata/devadol3/undotbs01.dbf";
   set newname for datafile  3 to
"/data/oracle/oradata/devadol3/sysaux01.dbf";
   set newname for datafile  4 to
"/data/oracle/oradata/devadol3/users01.dbf";
   backup as copy reuse
   datafile  1 auxiliary format
"/data/oracle/oradata/devadol3/system01.dbf"   datafile
2 auxiliary format
"/data/oracle/oradata/devadol3/undotbs01.dbf"   datafile
3 auxiliary format
"/data/oracle/oradata/devadol3/sysaux01.dbf"   datafile
4 auxiliary format
"/data/oracle/oradata/devadol3/users01.dbf"   datafile

;
   sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /data/oracle/oradata/devadol3/temp02.dbf in control file
renamed tempfile 2 to /data/oracle/oradata/devadol3/temp01.dbf in control file
renamed tempfile 3 to /data/oracle/oradata/devadol3/temp03.dbf in control file
renamed tempfile 4 to /data/oracle/oradata/devadol3/temp04.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME

Starting backup at 19-MAR-13
channel p1: starting datafile copy
input datafile file number=00002 name=/data/oracle/oradata/devadol3/undotbs01.dbf
channel p3: starting datafile copy
input datafile file number=00003 name=/data/oracle/oradata/devadol3/sysaux01.dbf
output file name=/data/oracle/oradata/devadol3/cpc12.dbf tag=TAG20130319T190323
channel p2: datafile copy complete, elapsed time: 00:05:38
input datafile file number=00001 name=/data/oracle/oradata/devadol3/system01.dbf
output file name=/data/oracle/oradata/devadol3/cpc04.dbf tag=TAG20130319T190323
channel p3: datafile copy complete, elapsed time: 00:01:15

Finished backup at 19-MAR-13
sql statement: alter system archive log current
contents of Memory Script.:
{
   backup as copy reuse
   archivelog like  "/data/oracle/oradata/devadol3/archive/1_21955_771089575.dbf" auxiliary format
"/data/oracle/oradata/devadol3/archive/1_21955_771089575.dbf"   archivelog like
"/data/oracle/oradata/devadol3/archive/1_21956_771089575.dbf" auxiliary format
"/data/oracle/oradata/devadol3/archive/1_21956_771089575.dbf"   ;
   catalog clone archivelog  "/data/oracle/oradata/devadol3/archive/1_21955_771089575.dbf";
   catalog clone archivelog  "/data/oracle/oradata/devadol3/archive/1_21956_771089575.dbf";
   switch clone datafile all;
}
executing Memory Script
Starting backup at 19-MAR-13
channel p1: starting archived log copy
input archived log thread=1 sequence=21955 RECID=43829 STAMP=810501149
channel p2: starting archived log copy
input archived log thread=1 sequence=21956 RECID=43830 STAMP=810501818
output file name=/data/oracle/oradata/devadol3/archive/1_21955_771089575.dbf RECID=0 STAMP=0
channel p1: archived log copy complete, elapsed time: 00:00:01
output file name=/data/oracle/oradata/devadol3/archive/1_21956_771089575.dbf RECID=0 STAMP=0
channel p2: archived log copy complete, elapsed time: 00:00:01
Finished backup at 19-MAR-13
cataloged archived log
archived log file name=/data/oracle/oradata/devadol3/archive/1_21955_771089575.dbf RECID=1 STAMP=810501870
cataloged archived log
archived log file name=/data/oracle/oradata/devadol3/archive/1_21956_771089575.dbf RECID=2 STAMP=810501870
datafile 1 switched to datafile copy
input datafile copy RECID=3 STAMP=810501870 file name=/data/oracle/oradata/devadol3/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=4 STAMP=810501870 file name=/data/oracle/oradata/devadol3/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=810501870 file name=/data/oracle/oradata/devadol3/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=810501870 file name=/data/oracle/oradata/devadol3/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=7 STAMP=810501870 file name=/data/oracle/oradata/devadol3/ad_tracer_log01.dbf

contents of Memory Script.:
{
   set until scn  8970477100730;
   recover
   standby
   clone database
    delete archivelog
   ;
}
executing Memory Script
executing command: SET until clause
Starting recover at 19-MAR-13
starting media recovery
archived log for thread 1 with sequence 21955 is already on disk as file /data/oracle/oradata/devadol3/archive/1_21955_771089575.dbf
archived log for thread 1 with sequence 21956 is already on disk as file /data/oracle/oradata/devadol3/archive/1_21956_771089575.dbf
archived log file name=/data/oracle/oradata/devadol3/archive/1_21955_771089575.dbf thread=1 sequence=21955
archived log file name=/data/oracle/oradata/devadol3/archive/1_21956_771089575.dbf thread=1 sequence=21956
media recovery complete, elapsed time: 00:00:01
Finished recover at 19-MAR-13
Finished Duplicate Db at 19-MAR-13

RMAN>
Recovery Manager complete.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/167606/viewspace-756592/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/167606/viewspace-756592/