Oracle 11G Data Guard通过Duplicate From Active Database 创建 Standby Database

Oracle 11G Data Guard
本文使用 Oracle11G Duplicate from active database 新方式创建DG物理备库,并且使用11G DG新特性 active data guard,实现real-time apply 和real-time quary
相关知识点阅读:http://opensgalaxy.com/2015/09/19/oracle-11g-data-guard-知识总结/
主库主机名:dgmaster01
数据库实例名:testdg
数据库服务名:DGM

备库主机名:dgstandby02
数据库实例名:testdg
数据库服务名:DGS

主库操作
1. 开启归档模式

SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;     

2. Primary 设置force logging

SQL> alter database force logging;
SQL> select force_logging from v$database;
FORCE_LOG
---------
YES

3、拷贝密码文件到备库
/u01/app/oracle/product/11.2.0/db_1/dbs/orapwtestdg
4、编辑参数文件追加如下内容:

*.db_unique_name='DGM'
*.log_archive_config='dg_config=(DGM,DGS)'
*.log_archive_dest_1='location=/u01/app/oracle/archivelog valid_for=(online_logfiles,all_roles) db_unique_name=DGM'
*.log_archive_dest_2='service=DGS reopen=120 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=DGS'
*.log_archive_dest_3='location=/u01/app/oracle/standbylog valid_for=(standby_logfiles,all_roles)'   ###如果你想把standby redo log 的归档路径与redo log的归档路径分开,就设置此参数,否则会放在一起
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.log_archive_dest_state_3=enable
*.standby_file_management='auto'
*.fal_server='DGS'
*.log_file_name_convert='/u01/app/oradata/testdg','/u01/app/oradata/testdg'   ###这个参数不设置,会出现在备库db_recovery_file_dest='/u01/app/oracle/fast_recovery_area 这个参数值路径下建立 redo log 和 standby log 文件  
##如果你想将redo log 和standby log存储到这个位置,还是加上吧,虽然主备库路径一致,这是我经过多次验证的。
*.db_file_name_convert='/u01/app/oradata/testdg','/u01/app/oradata/testdg'

备库操作
5、编辑参数文件追加如下内容:

*.db_unique_name='DGS'
*.log_archive_config='dg_config=(DGM,DGS)'
*.log_archive_dest_1='location=/u01/app/oracle/archivelog valid_for=(online_logfiles,all_roles) db_unique_name=DGS'
*.log_archive_dest_2='service=DGM reopen=120 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=DGM'
*.log_archive_dest_3='location=/u01/app/oracle/standbylog valid_for=(standby_logfiles,all_roles)'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.log_archive_dest_state_3=enable
*.standby_file_management='auto'
*.fal_server='DGM'
*.log_file_name_convert='/u01/app/oradata/testdg','/u01/app/oradata/testdg'
*.db_file_name_convert='/u01/app/oradata/testdg','/u01/app/oradata/testdg'

6、备库启动到nomount状态

SQL>create spfile from pfile;###pfile 文件防止在$ORACLE_HOME/dbs 下
SQL> startup nomount;

7、复制库

[oracle@dgmaster01 dbs]$ rman target sys/admin2012@DGM auxiliary sys/admin2012@DGS

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Sep 15 19:28:54 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TESTDG (DBID=2822661610)
connected to auxiliary database: TESTDG (not mounted)

RMAN> duplicate target database for standby from active database nofilenamecheck;

Starting Duplicate Db at 2015-09-15 19:30:52
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=10 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwtestdg' auxiliary format 
 '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwtestdg'   ;
}
executing Memory Script

Starting backup at 2015-09-15 19:30:53
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISK
Finished backup at 2015-09-15 19:30:55

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '/u01/app/oradata/testdg/control01.ctl';
   restore clone controlfile to  '/u01/app/oracle/fast_recovery_area/testdg/control02.ctl' from 
 '/u01/app/oradata/testdg/control01.ctl';
}
executing Memory Script

Starting backup at 2015-09-15 19:30:55
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_testdg.f tag=TAG20150915T193055 RECID=11 STAMP=890508655
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2015-09-15 19:30:56

Starting restore at 2015-09-15 19:30:56
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 2015-09-15 19:30:58

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
   set newname for tempfile  1 to 
 "/u01/app/oradata/testdg/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to 
 "/u01/app/oradata/testdg/system01.dbf";
   set newname for datafile  2 to 
 "/u01/app/oradata/testdg/sysaux01.dbf";
   set newname for datafile  3 to 
 "/u01/app/oradata/testdg/undotbs01.dbf";
   set newname for datafile  4 to 
 "/u01/app/oradata/testdg/users01.dbf";
   backup as copy reuse
   datafile  1 auxiliary format 
 "/u01/app/oradata/testdg/system01.dbf"   datafile 
 2 auxiliary format 
 "/u01/app/oradata/testdg/sysaux01.dbf"   datafile 
 3 auxiliary format 
 "/u01/app/oradata/testdg/undotbs01.dbf"   datafile 
 4 auxiliary format 
 "/u01/app/oradata/testdg/users01.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oradata/testdg/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 2015-09-15 19:31:03
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oradata/testdg/system01.dbf
output file name=/u01/app/oradata/testdg/system01.dbf tag=TAG20150915T193103
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oradata/testdg/sysaux01.dbf
output file name=/u01/app/oradata/testdg/sysaux01.dbf tag=TAG20150915T193103
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oradata/testdg/undotbs01.dbf
output file name=/u01/app/oradata/testdg/undotbs01.dbf tag=TAG20150915T193103
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oradata/testdg/users01.dbf
output file name=/u01/app/oradata/testdg/users01.dbf tag=TAG20150915T193103
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2015-09-15 19:31:28

sql statement: alter system archive log current

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=11 STAMP=890508690 file name=/u01/app/oradata/testdg/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=12 STAMP=890508690 file name=/u01/app/oradata/testdg/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=13 STAMP=890508690 file name=/u01/app/oradata/testdg/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=14 STAMP=890508690 file name=/u01/app/oradata/testdg/users01.dbf
Finished Duplicate Db at 2015-09-15 19:31:30


8、备库操作,开库并启动备库的实时应用

SQL> alter database open;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Database altered.

9、验证:

[oracle@dgmaster01 dbs]$ sqlplus  scott/tiger

SQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 15 19:34:46 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> insert into emp (empno,ename) values (1111,'zhanglong');

1 row created.

SQL> commit;

Commit complete.

SQL> select empno,ename from emp;

     EMPNO ENAME
---------- ----------

      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT

     EMPNO ENAME
---------- ----------
      7839 KING
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7902 FORD
      7934 MILLER
      1111 zhanglong

22 rows selected.

备库查询

[oracle@dgstandby02dbs]$ sqlplus  scott/tiger

SQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 15 19:34:46 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.
SQL> select empno,ename from emp;

     EMPNO ENAME
---------- ----------

      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT

     EMPNO ENAME
---------- ----------
      7839 KING
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7902 FORD
      7934 MILLER
      1111 zhanglong

22 rows selected.

切换日志


[oracle@dgmaster01 dbs]$ sqlplus  / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 15 20:02:11 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system switch logfile;

System altered.

[oracle@dgmaster01 archivelog]$ ll -tr
总用量 136656
-rw-r-----. 1 oracle oinstall 24087552 9月  14 14:08 1_19_889818284.dbf
-rw-r-----. 1 oracle oinstall    45568 9月  14 14:08 1_20_889818284.dbf
-rw-r-----. 1 oracle oinstall    25600 9月  14 14:09 1_21_889818284.dbf
-rw-r-----. 1 oracle oinstall  4728320 9月  14 15:14 1_22_889818284.dbf
-rw-r-----. 1 oracle oinstall    13312 9月  14 15:14 1_23_889818284.dbf
-rw-r-----. 1 oracle oinstall   237568 9月  14 15:16 1_24_889818284.dbf
-rw-r-----. 1 oracle oinstall   813056 9月  14 15:42 1_25_889818284.dbf
-rw-r-----. 1 oracle oinstall  6059520 9月  14 17:26 1_26_889818284.dbf
-rw-r-----. 1 oracle oinstall 39109632 9月  14 22:59 1_27_889818284.dbf
-rw-r-----. 1 oracle oinstall 39790080 9月  15 11:00 1_28_889818284.dbf
-rw-r-----. 1 oracle oinstall 18368000 9月  15 16:59 1_29_889818284.dbf
-rw-r-----. 1 oracle oinstall    52224 9月  15 17:00 1_30_889818284.dbf
-rw-r-----. 1 oracle oinstall   285184 9月  15 17:08 1_31_889818284.dbf
-rw-r-----. 1 oracle oinstall    11776 9月  15 17:08 1_32_889818284.dbf
-rw-r-----. 1 oracle oinstall   830464 9月  15 17:11 1_33_889818284.dbf
-rw-r-----. 1 oracle oinstall   778752 9月  15 17:21 1_34_889818284.dbf
-rw-r-----. 1 oracle oinstall  1856512 9月  15 19:14 1_37_889818284.dbf
-rw-r-----. 1 oracle oinstall    46080 9月  15 19:14 1_38_889818284.dbf
-rw-r-----. 1 oracle oinstall   121344 9月  15 19:18 1_39_889818284.dbf
-rw-r-----. 1 oracle oinstall   334336 9月  15 19:28 1_40_889818284.dbf
-rw-r-----. 1 oracle oinstall    50176 9月  15 19:28 1_41_889818284.dbf
-rw-r-----. 1 oracle oinstall    82944 9月  15 19:31 1_42_889818284.dbf
-rw-r-----. 1 oracle oinstall    12800 9月  15 19:31 1_43_889818284.dbf
-rw-r-----. 1 oracle oinstall    62976 9月  15 19:33 1_44_889818284.dbf
-rw-r-----. 1 oracle oinstall  2075648 9月  15 20:02 1_45_889818284.dbf

[oracle@dgstandby02 standbylog]$ ll -tr
总用量 2192
-rw-r-----. 1 oracle oinstall   12800 9月  15 19:33 1_43_889818284.dbf
-rw-r-----. 1 oracle oinstall   82944 9月  15 19:33 1_42_889818284.dbf
-rw-r-----. 1 oracle oinstall   62976 9月  15 19:33 1_44_889818284.dbf
-rw-r-----. 1 oracle oinstall 2075648 9月  15 20:02 1_45_889818284.dbf

日志也已经传送到了备库目录。

发表评论