oracle 11g duplicate active database 建立物理DG

环境：

primary:

OS: Red Hat Enterprise　5.8

IP: 10.131.119.118

DBVersion: 11.2.0.3.0

db_name: orcl

db_unique_name: orcl

standby:

OS: Red Hat Enterprise　5.8

IP: 10.131.119.119

DBVersion: 11.2.0.3.0

db_name: orcl

db_unique_name: std

步骤1：将主库设置为归档模式与force_logging模式

SQL>shutdown immediate;

SQL>startup mount;

SQL>alter database archivelog;

SQL>alter database open;

SQL>alter database force logging;

SQL> select DBID,NAME,LOG_MODE,FORCE_LOGGING from v$database;

      DBID NAME      LOG_MODE     FOR

---------- --------- ------------ ---

1414106639 ORCL      ARCHIVELOG   YES

步骤2：将主库的pfile文件和密码文件拷备到备库

[oracle@CS-SI-DG01 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 27 16:05:21 2016

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create pfile from spfile;

File created.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@CS-SI-DG01 dbs]$ scp initorcl.ora 10.131.119.119:/u2/oracle/product/11.2.0/dbhome_1/dbs/initstd.ora

oracle@10.131.119.119's password: 

stty: standard input: Invalid argument

initorcl.ora                                     100% 1112     1.1KB/s   00:00    

[oracle@CS-SI-DG01 dbs]$ scp orapworcl 10.131.119.119:/u2/oracle/product/11.2.0/dbhome_1/dbs/orapwstd

oracle@10.131.119.119's password: 

stty: standard input: Invalid argument

orapworcl                                        100% 1536     1.5KB/s   00:00 

步骤3：设置主库的监听与TNS

监听：

[oracle@CS-SI-DG01 admin]$ cat listener.ora 

# listener.ora Network Configuration File: /u2/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = orcl)

      (ORACLE_HOME = /u2/oracle/product/11.2.0/dbhome_1)

      (SID_NAME = orcl)

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.131.119.118)(PORT = 1521))

    )

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

ADR_BASE_LISTENER = /u2/oracle

[oracle@CS-SI-DG01 ~]$ lsnrctl reload

TNS：

[oracle@CS-SI-DG01 admin]$ cat tnsnames.ora 

# tnsnames.ora Network Configuration File: /u2/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

ORCL =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.131.119.118)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

std =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.131.119.119)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = std)

    )

  )

步骤4：修改主库的参数

[oracle@CS-SI-DG01 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 27 16:19:40 2016

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system set log_archive_config='dg_config=(orcl,std)';

System altered.

SQL> alter system set log_archive_dest_2='SERVICE=std ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=std';

System altered.

SQL> alter system set log_archive_dest_state_2=enable;

System altered.

SQL> alter system set log_archive_dest_state_1=enable;

System altered.

SQL> alter system set fal_server='std';

System altered.

SQL> alter system set fal_client='orcl';

System altered.

SQL> alter system set standby_file_management='auto';

System altered.

注：修改好参数后重新启动数据库

步骤5：修改备库参数文件

[oracle@CS-SI-DG02 dbs]$ cat initstd.ora 

*._undo_autotune=TRUE

*.audit_file_dest='/u2/oracle/admin/std/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/oradata/std/control01.ctl','/oradata/std/control02.ctl'#Restore Controlfile

*.db_block_size=8192

*.db_domain=''

*.db_name='orcl'

*.db_recovery_file_dest='/u2/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=8589934592

*.diagnostic_dest='/u2/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=stdXDB)'

*.job_queue_processes=1000

*.log_archive_dest_1='location=/oradata/arch/std'

*.log_archive_dest_10=' '

*.memory_max_target=0

*.memory_target=0

*.open_cursors=300

*.pga_aggregate_target=171966464

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.session_cached_cursors=240

*.sga_target=335544320

*.undo_retention=60

*.undo_tablespace='UNDOTBS2'

*.log_archive_dest_2='SERVICE=orcl ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'

*.log_archive_config='dg_config=(orcl,std)'

*.log_archive_dest_state_2=enable

*.log_archive_dest_state_1=enable

*.fal_server='orcl'

*.fal_client='std'

*.standby_file_management='auto'

*.log_file_name_convert=('/oradata/orcl','/oradata/std')

*.db_file_name_convert=('/oradata/orcl','/oradata/std')

*.db_unique_name='std'

步骤6：在备库创建目录

[oracle@CS-SI-DG02 dbs]$ mkdir -p /u2/oracle/admin/std/adump

[oracle@CS-SI-DG02 dbs]$ mkdir -p /oradata/std/

[oracle@CS-SI-DG02 dbs]$ mkdir -p /oradata/arch/std

步骤7：设置备库的监听与TNS

监听：

[oracle@CS-SI-DG02 admin]$ cat listener.ora 

# listener.ora Network Configuration File: /u2/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = std)

      (ORACLE_HOME = /u2/oracle/product/11.2.0/dbhome_1)

      (SID_NAME = std)

    )

  )

ADR_BASE_LISTENER = /u2/oracle

STENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.131.119.119)(PORT = 1521))

    )

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

[oracle@CS-SI-DG02 admin]$ lsnrctl reload

TNS：

[oracle@CS-SI-DG02 admin]$ cat tnsnames.ora 

# tnsnames.ora Network Configuration File: /u2/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

ORCL =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.131.119.118)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = orcl)

    )

  )

std =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.131.119.119)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = std)

    )

  )

步骤8：将备库启动到nomount状态

[oracle@CS-SI-DG02 admin]$ export ORACLE_SID=std

[oracle@CS-SI-DG02 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 27 16:56:58 2016

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

Connected to an idle instance.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area  509411328 bytes

Fixed Size                  2229824 bytes

Variable Size             285215168 bytes

Database Buffers          213909504 bytes

Redo Buffers                8056832 bytes

步骤9：在备库恢复数据库

[oracle@CS-SI-DG02 admin]$ rman target sys/oracle@orcl auxiliary sys/oracle@std nocatalog

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jan 27 17:30:29 2016

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

connected to target database: ORCL (DBID=1414106639)

using target database control file instead of recovery catalog

connected to auxiliary database: ORCL (not mounted)

RMAN> duplicate target database for standby from active database;

Starting Duplicate Db at 27-JAN-16

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=134 device type=DISK

contents of Memory Script:

{

   backup as copy reuse

   targetfile  '/u2/oracle/product/11.2.0/dbhome_1/dbs/orapworcl' auxiliary format 

 '/u2/oracle/product/11.2.0/dbhome_1/dbs/orapwstd'   ;

}

executing Memory Script

Starting backup at 27-JAN-16

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=22 device type=DISK

Finished backup at 27-JAN-16

contents of Memory Script:

{

   backup as copy current controlfile for standby auxiliary format  '/oradata/std/control01.ctl';

   restore clone controlfile to  '/oradata/std/control02.ctl' from 

 '/oradata/std/control01.ctl';

}

executing Memory Script

Starting backup at 27-JAN-16

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

copying standby control file

output file name=/u2/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f tag=TAG20160127T173104 RECID=25 STAMP=902251864

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 27-JAN-16

Starting restore at 27-JAN-16

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy

Finished restore at 27-JAN-16

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 

 "/oradata/std/temp01.dbf";

   switch clone tempfile all;

   set newname for datafile  1 to 

 "/oradata/std/system01.dbf";

   set newname for datafile  2 to 

 "/oradata/std/sysaux01.dbf";

   set newname for datafile  3 to 

 "/oradata/std/test01.dbf";

   set newname for datafile  4 to 

 "/oradata/std/users01.dbf";

   set newname for datafile  6 to 

 "/oradata/std/undotbs02.dbf";

   set newname for datafile  7 to 

 "/oradata/std/tbspart01.dbf";

   set newname for datafile  8 to 

 "/oradata/std/tbspart02.dbf";

   set newname for datafile  9 to 

 "/oradata/std/tbspart03.dbf";

   set newname for datafile  11 to 

 "/oradata/std/assm01.dbf";

   set newname for datafile  12 to 

 "/oradata/std/tbspart04.dbf";

   set newname for datafile  13 to 

 "/oradata/std/rman01.dbf";

   set newname for datafile  14 to 

 "/oradata/std/test1.dbf";

   set newname for datafile  15 to 

 "/oradata/std/test2.dbf";

   set newname for datafile  16 to 

 "/oradata/std/linktbs01.dbf";

   set newname for datafile  17 to 

 "/oradata/std/ogg01.dbf";

   set newname for datafile  18 to 

 "/oradata/std/tom101.dbf";

   backup as copy reuse

   datafile  1 auxiliary format 

 "/oradata/std/system01.dbf"   datafile 

 2 auxiliary format 

 "/oradata/std/sysaux01.dbf"   datafile 

 3 auxiliary format 

 "/oradata/std/test01.dbf"   datafile 

 4 auxiliary format 

 "/oradata/std/users01.dbf"   datafile 

 6 auxiliary format 

 "/oradata/std/undotbs02.dbf"   datafile 

 7 auxiliary format 

 "/oradata/std/tbspart01.dbf"   datafile 

 8 auxiliary format 

 "/oradata/std/tbspart02.dbf"   datafile 

 9 auxiliary format 

 "/oradata/std/tbspart03.dbf"   datafile 

 11 auxiliary format 

 "/oradata/std/assm01.dbf"   datafile 

 12 auxiliary format 

 "/oradata/std/tbspart04.dbf"   datafile 

 13 auxiliary format 

 "/oradata/std/rman01.dbf"   datafile 

 14 auxiliary format 

 "/oradata/std/test1.dbf"   datafile 

 15 auxiliary format 

 "/oradata/std/test2.dbf"   datafile 

 16 auxiliary format 

 "/oradata/std/linktbs01.dbf"   datafile 

 17 auxiliary format 

 "/oradata/std/ogg01.dbf"   datafile 

 18 auxiliary format 

 "/oradata/std/tom101.dbf"   ;

   sql 'alter system archive log current';

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /oradata/std/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 27-JAN-16

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002 name=/oradata/orcl/sysaux01.dbf

output file name=/oradata/std/sysaux01.dbf tag=TAG20160127T173112

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:06

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=/oradata/orcl/system01.dbf

output file name=/oradata/std/system01.dbf tag=TAG20160127T173112

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:46

channel ORA_DISK_1: starting datafile copy

input datafile file number=00006 name=/oradata/orcl/undotbs02.dbf

output file name=/oradata/std/undotbs02.dbf tag=TAG20160127T173112

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003 name=/oradata/orcl/test01.dbf

output file name=/oradata/std/test01.dbf tag=TAG20160127T173112

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile copy

input datafile file number=00011 name=/oradata/orcl/assm01.dbf

output file name=/oradata/std/assm01.dbf tag=TAG20160127T173112

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile copy

input datafile file number=00017 name=/oradata/orcl/ogg01.dbf

output file name=/oradata/std/ogg01.dbf tag=TAG20160127T173112

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile copy

input datafile file number=00018 name=/oradata/orcl/tom101.dbf

output file name=/oradata/std/tom101.dbf tag=TAG20160127T173112

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=/oradata/orcl/users01.dbf

output file name=/oradata/std/users01.dbf tag=TAG20160127T173112

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile copy

input datafile file number=00007 name=/oradata/orcl/tbspart01.dbf

output file name=/oradata/std/tbspart01.dbf tag=TAG20160127T173112

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

channel ORA_DISK_1: starting datafile copy

input datafile file number=00008 name=/oradata/orcl/tbspart02.dbf

output file name=/oradata/std/tbspart02.dbf tag=TAG20160127T173112

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

channel ORA_DISK_1: starting datafile copy

input datafile file number=00009 name=/oradata/orcl/tbspart03.dbf

output file name=/oradata/std/tbspart03.dbf tag=TAG20160127T173112

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

channel ORA_DISK_1: starting datafile copy

input datafile file number=00012 name=/oradata/orcl/tbspart04.dbf

output file name=/oradata/std/tbspart04.dbf tag=TAG20160127T173112

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

channel ORA_DISK_1: starting datafile copy

input datafile file number=00013 name=/oradata/orcl/rman01.dbf

output file name=/oradata/std/rman01.dbf tag=TAG20160127T173112

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

channel ORA_DISK_1: starting datafile copy

input datafile file number=00014 name=/oradata/orcl/test1.dbf

output file name=/oradata/std/test1.dbf tag=TAG20160127T173112

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

channel ORA_DISK_1: starting datafile copy

input datafile file number=00015 name=/oradata/orcl/test2.dbf

output file name=/oradata/std/test2.dbf tag=TAG20160127T173112

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

channel ORA_DISK_1: starting datafile copy

input datafile file number=00016 name=/oradata/orcl/linktbs01.dbf

output file name=/oradata/std/linktbs01.dbf tag=TAG20160127T173112

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 27-JAN-16

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=25 STAMP=902252046 file name=/oradata/std/system01.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=26 STAMP=902252046 file name=/oradata/std/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=27 STAMP=902252046 file name=/oradata/std/test01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=28 STAMP=902252046 file name=/oradata/std/users01.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=29 STAMP=902252046 file name=/oradata/std/undotbs02.dbf

datafile 7 switched to datafile copy

input datafile copy RECID=30 STAMP=902252046 file name=/oradata/std/tbspart01.dbf

datafile 8 switched to datafile copy

input datafile copy RECID=31 STAMP=902252046 file name=/oradata/std/tbspart02.dbf

datafile 9 switched to datafile copy

input datafile copy RECID=32 STAMP=902252046 file name=/oradata/std/tbspart03.dbf

datafile 11 switched to datafile copy

input datafile copy RECID=33 STAMP=902252046 file name=/oradata/std/assm01.dbf

datafile 12 switched to datafile copy

input datafile copy RECID=34 STAMP=902252046 file name=/oradata/std/tbspart04.dbf

datafile 13 switched to datafile copy

input datafile copy RECID=35 STAMP=902252046 file name=/oradata/std/rman01.dbf

datafile 14 switched to datafile copy

input datafile copy RECID=36 STAMP=902252046 file name=/oradata/std/test1.dbf

datafile 15 switched to datafile copy

input datafile copy RECID=37 STAMP=902252046 file name=/oradata/std/test2.dbf

datafile 16 switched to datafile copy

input datafile copy RECID=38 STAMP=902252046 file name=/oradata/std/linktbs01.dbf

datafile 17 switched to datafile copy

input datafile copy RECID=39 STAMP=902252046 file name=/oradata/std/ogg01.dbf

datafile 18 switched to datafile copy

input datafile copy RECID=40 STAMP=902252046 file name=/oradata/std/tom101.dbf

Finished Duplicate Db at 27-JAN-16

步骤10：在备库创建standby日志

[oracle@CS-SI-DG02 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 27 18:06:26 2016

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select open_mode from v$database;

OPEN_MODE

--------------------

MOUNTED

SQL> select * from v$logfile;

    GROUP# STATUS     TYPE       MEMBER                         IS_

---------- ---------- ---------- ------------------------------ ---

1           ONLINE     /oradata/std/redo03.log        NO

2           ONLINE     /oradata/std/redo02.log        NO

3            ONLINE     /oradata/std/redo01.log        NO

3 rows selected.

SQL> alter database add standby logfile group 4 '/oradata/std/stb_redo04.log' size 50M;

Database altered.

SQL> alter database add standby logfile group 5 '/oradata/std/stb_redo5.log' size 50M;

Database altered.

SQL> alter database add standby logfile group 6 '/oradata/std/stb_redo06.log' size 50M 

  2  ;

Database altered.

SQL> alter database add standby logfile group 7 '/oradata/std/stb_redo07.log' size 50M;

Database altered.

SQL> select * from v$logfile;

    GROUP# STATUS     TYPE       MEMBER                         IS_

---------- ---------- ---------- ------------------------------ ---

1           ONLINE     /oradata/std/redo03.log        NO

2           ONLINE     /oradata/std/redo02.log        NO

3           ONLINE     /oradata/std/redo01.log        NO

4           STANDBY    /oradata/std/stb_redo04.log    NO

5           STANDBY    /oradata/std/stb_redo5.log     NO

6           STANDBY    /oradata/std/stb_redo06.log    NO

7           STANDBY    /oradata/std/stb_redo07.log    NO

7 rows selected.

步骤11：启动DG

[oracle@CS-SI-DG02 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 27 18:19:34 2016

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database open read only;

Database altered.

SQL> alter database recover managed standby database disconnect from session using current logfile;

Database altered.