oracle 11G ADG添加一个备库组成一主库两备库模式

概述：有时数据库需要多个物理备库以解决各个问题，这里就对刚刚搭建好的ADG 新增一个dg数据库以作备忘；
SQL> select open_mode,PROTECTION_LEVEL,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;

OPEN_MODE            PROTECTION_LEVEL     DATABASE_ROLE    SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
READ ONLY WITH APPLY MAXIMUM PERFORMANCE  PHYSICAL STANDBY NOT ALLOWED

SQL> ! hostname
11g-adg

SQL> select open_mode,PROTECTION_LEVEL,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;

OPEN_MODE            PROTECTION_LEVEL     DATABASE_ROLE    SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
READ WRITE           MAXIMUM PERFORMANCE  PRIMARY          SESSIONS ACTIVE

SQL> SQL> ! hostname
11g-rac1

主库修改参数：

SQL> alter system set db_file_name_convert ='/u01/ora/app/oradata/cube/','+DATADG/cube/datafile/','/u01/ora/app/oradata/cube/','+DATADG/cube/tempfile/','+data/cubedg/datafile','+DATADG/cube/datafile/','+data/cubedg/tempfile','+DATADG/cube/tempfile/' scope=spfile sid='*';

System altered.
SQL> alter system set log_file_name_convert='/u01/ora/app/oradata/cube/','+DATADG/cube/onlinelog/','+data/cubedg/onlinelog/','+DATADG/cube/onlinelog/' scope=spfile sid='*';

System altered.

SQL> alter system set log_archive_dest_3='SERVICE=cubedg ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)  DB_UNIQUE_NAME=cubedg' scope=both sid='*';
System altered.

SQL> SQL>  alter system set fal_client=cubep scope=both sid='*'; 

System altered.

SQL> alter system set fal_server='CUBEADG','CUBEDG' scope=both sid='*';

System altered.

SQL>  alter system set log_archive_config='DG_CONFIG=(cubep,cubeadg,cubedg)'  scope=bothsid='*';

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_STATE_3='ENABLE' scope=both sid='*';

System altered.

ADG端修改参数：

SQL> alter system set fal_client='cubeadg';

System altered.

SQL> alter system set fal_server='cubep','cubedg';

System altered.

SQL> alter system set log_archive_config='DG_CONFIG=(cubep,cubeadg,cubedg)';

System altered.

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

System altered.

SQL>  alter system set LOG_ARCHIVE_DEST_STATE_3='ENABLE';

System altered.

SQL> alter system set db_file_name_convert='+DATADG/cube/datafile/','/u01/ora/app/oradata/cube/','+DATADG/cube/tempfile/','/u01/ora/app/oradata/cube/','+data/cube/data','/u01/ora/app/oradata/cube/' scope=spfile;

System altered.

SQL> alter system set log_file_name_convert='+DATADG/cube/onlinelog/','/u01/ora/app/oradata/cube/','+data/cube/data','/u01/ora/app/oradata/cube/' scope=spfile;

System altered.

第二备库 DG端参数并建立对应的目录：

[root@cube2 dbs]# cat initcube.ora 
*.audit_file_dest='/u01/app/oracle/admin/cube/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='+data/cube/data/control01.ctl','+data/cube/data/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='+DATADG/cube/datafile/','/u01/ora/app/oradata/cube/','+DATADG/cube/tempfile/','/u01/ora/app/oradata/cube/','+data/cubedg/datafile','/u01/ora/app/oradata/cube/','+data/cubedg/tempfile/','/u01/ora/app/oradata/cube/'
*.db_name='cube'
*.db_recovery_file_dest='+data'
*.db_recovery_file_dest_size=4621074432
*.DB_UNIQUE_NAME='cubeadg'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=cubeXDB)'
*.fal_client='cubedg'
*.fal_server='cubep','cubeadg'
*.log_archive_config='DG_CONFIG=(cubep,cubeadg,cubedg)'
*.LOG_ARCHIVE_DEST_1='LOCATION=+data/cube/archivelog
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=cubedg'
*.LOG_ARCHIVE_DEST_2='SERVICE=cubep ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) 
  DB_UNIQUE_NAME=cubep'
*.log_archive_dest_3='SERVICE=cubeadg ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)  DB_UNIQUE_NAME=cubedg'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.log_archive_dest_state_3='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert='+DATADG/cube/onlinelog/','/u01/ora/app/oradata/cube/','+data/cubedg/onlinelog','/u01/ora/app/oradata/cube/'
*.memory_target=838860800
*.open_cursors=300
*.processes=150
*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.undo_tablespace='UNDOTBS1'

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2257840 bytes
Variable Size             541068368 bytes
Database Buffers          289406976 bytes
Redo Buffers                2371584 bytes
SQL> create spfile from pfile;

File created.

SQL> startup nomount force;
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2257840 bytes
Variable Size             541068368 bytes
Database Buffers          289406976 bytes
Redo Buffers                2371584 bytes
SQL> 

[oracle@11g-adg dbs]$ scp orapwcube 192.168.56.10:/u01/app/oracle/product/11.2.0/db_1/dbs/
The authenticity of host '192.168.56.10 (192.168.56.10)' can't be established.
RSA key fingerprint is a8:48:04:d8:66:7a:72:64:c6:d2:8b:6b:71:58:87:75.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.56.10' (RSA) to the list of known hosts.
oracle@192.168.56.10's password: 
orapwcube                                                                                100% 1536     1.5KB/s   00:00    
 

tns文件listner文件配置：
[root@cube2 admin]# cat tnsnames.ora 
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
cubep =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.20)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.22)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cubep)
    )
)
cubeadg =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.12)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cubeadg)
      (UR=A)
    )
)
cubedg =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.10)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cubedg)
      (UR=A)
    )
)

 

[oracle@cube2 admin]$ rman target sys/windows@cubep auxiliary sys/windows@cubedg

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Oct 16 11:55:01 2018

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

connected to target database: CUBE (DBID=3170299392)
connected to auxiliary database: CUBE (not mounted)

RMAN> DUPLICATE TARGET DATABASE
2>   FOR STANDBY
3>   FROM ACTIVE DATABASE;

Starting Duplicate Db at 16-OCT-18
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=25 device type=DISK

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

Starting backup at 16-OCT-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=58 instance=cube1 device type=DISK
Finished backup at 16-OCT-18

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '+DATA/cube/data/control01.ctl';
   restore clone primary controlfile to  '+DATA/cube/data/control02.ctl' from 
 '+DATA/cube/data/control01.ctl';
}
executing Memory Script

Starting backup at 16-OCT-18
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.4/db_1/dbs/snapcf_cube1.f tag=TAG20181016T115516 RECID=3 STAMP=989668518
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 16-OCT-18

Starting restore at 16-OCT-18
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 16-OCT-18

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

sql statement: alter database mount standby database
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.

contents of Memory Script:
{
   set newname for tempfile  1 to 
 "+data";
   switch clone tempfile all;
   set newname for datafile  1 to 
 "+data";
   set newname for datafile  2 to 
 "+data";
   set newname for datafile  3 to 
 "+data";
   set newname for datafile  4 to 
 "+data";
   set newname for datafile  5 to 
 "+data";
   backup as copy reuse
   datafile  1 auxiliary format 
 "+data"   datafile 
 2 auxiliary format 
 "+data"   datafile 
 3 auxiliary format 
 "+data"   datafile 
 4 auxiliary format 
 "+data"   datafile 
 5 auxiliary format 
 "+data"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +data in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 16-OCT-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATADG/cube/datafile/system.256.986815411
output file name=+DATA/cubedg/datafile/system.284.989668529 tag=TAG20181016T115528
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATADG/cube/datafile/sysaux.257.986815411
output file name=+DATA/cubedg/datafile/sysaux.283.989668563 tag=TAG20181016T115528
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATADG/cube/datafile/undotbs1.258.986815413
output file name=+DATA/cubedg/datafile/undotbs1.271.989668589 tag=TAG20181016T115528
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DATADG/cube/datafile/undotbs2.267.986815775
output file name=+DATA/cubedg/datafile/undotbs2.275.989668597 tag=TAG20181016T115528
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATADG/cube/datafile/users.259.986815413
output file name=+DATA/cubedg/datafile/users.261.989668599 tag=TAG20181016T115528
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 16-OCT-18

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=3 STAMP=989668606 file name=+DATA/cubedg/datafile/system.284.989668529
datafile 2 switched to datafile copy
input datafile copy RECID=4 STAMP=989668606 file name=+DATA/cubedg/datafile/sysaux.283.989668563
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=989668606 file name=+DATA/cubedg/datafile/undotbs1.271.989668589
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=989668606 file name=+DATA/cubedg/datafile/users.261.989668599
datafile 5 switched to datafile copy
input datafile copy RECID=7 STAMP=989668606 file name=+DATA/cubedg/datafile/undotbs2.275.989668597
Finished Duplicate Db at 16-OCT-18

RMAN> 

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

Database altered.

SQL> select open_mode,PROTECTION_LEVEL,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;

OPEN_MODE            PROTECTION_LEVEL     DATABASE_ROLE    SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
READ ONLY WITH APPLY MAXIMUM PERFORMANCE  PHYSICAL STANDBY NOT ALLOWED

 

查看主数据、adg数据库、dg数据库  物理备库状况：

SQL> select inst_id,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;

   INST_ID CURRENT_SCN PROTECTION_MODE      DATABASE_ROLE    FOR OPEN_MODE            SWITCHOVER_STATUS
---------- ----------- -------------------- ---------------- --- -------------------- --------------------
         2     1170202 MAXIMUM PERFORMANCE  PRIMARY          YES READ WRITE           SESSIONS ACTIVE
         1     1170202 MAXIMUM PERFORMANCE  PRIMARY          YES READ WRITE           SESSIONS ACTIVE

SQL> select open_mode,PROTECTION_LEVEL,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;

OPEN_MODE            PROTECTION_LEVEL     DATABASE_ROLE    SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
READ ONLY WITH APPLY MAXIMUM PERFORMANCE  PHYSICAL STANDBY NOT ALLOWED

 

总结：第三节点ADG搭建完成，如何使用dg broker管理多个dg呢？